SQL Server 迭代数百万行的更好方法 [英] SQL Server better way to iterate through millions of rows
本文介绍了SQL Server 迭代数百万行的更好方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在处理 SAP Timesheet 数据，因此有数百万行.我想要做的是从 SAP 表中选择数据并将其插入到 MS SQL Server 上的表中.
所以我想插入原始记录，然后如果对原始记录的更新发生，这是一个带有refcounter的新SAP记录的形式，我想找到原始记录在我的表中并更新它，保持原始计数器值.
所以我用游标成功地做到了这一点(我知道不是最好的)，但是有数百万条记录，我想知道是否有更快的方法，因为我在游标运行的第 4 天.有没有比我下面更好的方法:
BEGIN创建表CATSDB([计数器] nvarchar(12),REFCOUNTER nvarchar(12),PERNR nvarchar(8),工作日期 nvarchar(8),CATSHOURS 十进制(7, 3),APDAT nvarchar(8),LAETM nvarchar(6),CATS_STATUS nvarchar(2),APPR_STATUS nvarchar(2))插入CATSDB([COUNTER],REFCOUNTER,PERNR,WORKDATE,CATSHOURS,APDAT,LAETM,CATS_STATUS,APPR_STATUS)价值观('000421692670',NULL,'00000071','20190114','6.00','20190204','174541','30','30'),('000421692671',NULL,'00000071','20190114','3.00','20190204','174541','30','30'),('000421692672',NULL,'00000071','20190115','6.00','00000000','000000','60','20'),('000421692673',NULL,'00000071','20190115','3.00','00000000','000000','60','20'),('000421692712','000421692672','00000071','20190115','0.00','20190115','111007','30','30'),('000421692713','000421692673','00000071','20190115','0.00','20190115','111007','30','30'),('000429718015',NULL,'00000072','20190313','7.00','00000000','000000','60','20'),('000429718016',NULL,'00000072','20190313','1.50','20190315','164659','30','30'),('000429718017',NULL,'00000072','20190313','1.00','20190315','164659','30','30'),('000430154143',NULL,'00000072','20190313','2.00','00000000','000000','60','20'),('000430154142','000429718015','00000072','20190313','5.00','00000000','000000','60','20'),('000430154928','000430154142','00000072','20190313','4.50','20190315','164659','30','30'),('000430154929','000430154143','00000072','20190313','2.50','20190315','164659','30','30'),('000429774620',NULL,'00000152','20190314','1.00','00000000','000000','60','20'),('000429774619',NULL,'00000152','20190314','1.00','00000000','000000','60','20'),('000429802106','000429774620','00000152','20190314','2.00','00000000','000000','60','20'),('000429802105','000429774619','00000152','20190314','3.00','00000000','000000','60','20'),('000429840242','000429802106','00000152','20190314','4.00','20190315','143857','30','30'),('000429840241','000429802105','00000152','20190314','5.00','20190315','143857','30','30')创建表 [TBL_COUNTER]([计数器] [varchar](12) 非空，[REFCOUNTER] [varchar](12) NULL)创建表温度([计数器] [nvarchar](12) 非空，[REFCOUNTER] [nvarchar](12) NULL，[PERNR] [nvarchar](8) NULL，[工作日期] [nvarchar](8) NULL，[CATSHOURS] [十进制](7, 3) NULL,[APDAT] [nvarchar](8) NULL,[LAETM] [nvarchar](6) NULL，[CATS_STATUS] [nvarchar](2) NULL，[APPR_STATUS] [nvarchar](2) NULL)结尾开始声明@COUNTER nvarchar(12),@REFCOUNTER nvarchar(12),@PERNR nvarchar(8),@WORKDATE nvarchar(8),@CATSHOURS 十进制(7, 3),@APDAT nvarchar(8),@LAETM nvarchar(6)，@CATS_STATUS nvarchar(2),@APPR_STATUS nvarchar(2)声明@orig_counter nvarchar(12)结尾开始声明curs CURSOR FOR选择[柜台]，参考，PERNR，工作日期，猫舍，亚太地区，LAETM,CATS_STATUS,APPR_STATUS从CATSDB结尾开始开放诅咒结尾开始FETCH NEXT FROM curs INTO@柜台，@REFCOUNTER，@PERNR，@工作日期，@CATSHOURS，@APDAT，@LAETM，@CATS_STATUS，@APPR_STATUS结尾开始而@@FETCH_STATUS = 0开始开始如果不存在(SELECT * FROM TBL_COUNTER WHERE [COUNTER] = @COUNTER)开始插入 TBL_COUNTER([柜台],REFCOUNTER)价值观(@柜台,@REFCOUNTER)结尾结尾开始如果不存在(SELECT * FROM TEMP WHERE [COUNTER] = @COUNTER)开始-- 如果 REFCOUNTER 已填充，则获取原始 COUNTER 值，然后使用新值更新该行.否则插入新记录如果@REFCOUNTER <>'' 并且@REFCOUNTER 不为空开始开始WITH n([COUNTER], REFCOUNTER) AS(选择cnt.[计数器],cnt.REFCOUNTER从TBL_COUNTER 计数在哪里cnt.[计数器] = @REFCOUNTER联合所有选择nplus1.[计数器]，nplus1.REFCOUNTER从TBL_COUNTER 为 nplus1，n在哪里n.[计数器] = nplus1.REFCOUNTER)SELECT @orig_counter = [COUNTER] FROM n WHERE REFCOUNTER = '' OR REFCOUNTER IS NULL结尾开始更新温度放[引用计数器] = @引用计数器,[PERNR] = @PERNR,[工作日期] = @工作日期,[CATSHOURS] = @CATSHOURS,[APDAT] = @APDAT,[LAETM] = @LAETM,[CATS_STATUS] = @CATS_STATUS,[APPR_STATUS] = @APPR_STATUSWHERE [计数器] = @orig_counter结尾结尾别的开始插入温度([柜台],[参考],[PERNR],[工作日期],[猫舍],[应用程序],[莱特姆],[CATS_STATUS],[APPR_STATUS])价值观(@柜台,@引用计数器,@PERNR,@工作日期,@CATSHOURS,@APDAT,@LAETM,@CATS_STATUS,@APPR_STATUS)结尾结尾FETCH NEXT FROM curs INTO@柜台，@REFCOUNTER，@PERNR，@工作日期，@CATSHOURS，@APDAT，@LAETM，@CATS_STATUS，@APPR_STATUS结尾结尾结尾开始关闭光标解除分配curs结尾我缩短了它并为你们所有人创建了表格，以便能够看到发生了什么.预期的结果是
+--------------+--------------+----------+----------+-----------+----------+--------+-------------+------------+|计数器 |REFCOUNTER |PERNR |工作日期 |猫舍 |APDAT |LAETM |CATS_STATUS |APPR_STATUS |+--------------+--------------+---------+----------+-----------+----------+--------+------------+--------------+|000421692670 |空 |00000071 |20190114 |6.00 |20190204 |174541 |30 |30 ||000421692671 |空 |00000071 |20190114 |3.00 |20190204 |174541 |30 |30 ||000421692672 |000421692672 |00000071 |20190115 |0.00 |20190115 |111007 |30 |30 ||000421692673 |000421692673 |00000071 |20190115 |0.00 |20190115 |111007 |30 |30 ||000429718015 |000430154142 |00000072 |20190313 |4.50 |20190315 |164659 |30 |30 ||000429718016 |空 |00000072 |20190313 |1.50 |20190315 |164659 |30 |30 ||000429718017 |空 |00000072 |20190313 |1.0 |20190315 |164659 |30 |30 ||000430154143 |000430154143 |00000072 |20190313 |2.50 |20190315 |164659 |30 |30 ||000429774620 |000429774620 |00000152 |20190314 |2.00 |00000000 |000000 |60 |20 ||000429774619 |000429802105 |00000152 |20190314 |5.00 |20190315 |143857 |30 |30 |+--------------+--------------+---------+----------+-----------+----------+--------+------------+--------------+我需要补充一下.所以这有两个阶段.第一阶段是我将拉取 2019 年的所有数据，用于我的表的初始加载.然后每周，我将从原始源中提取数据以获取新记录和上次运行时更改的记录.所以我不会每周都有完整的链条.需要有一种方法可以在没有完整数据集的情况下返回原始计数器值，这就是为什么我有计数器表的原因.我很抱歉没有更清楚.我忙于工作，无法像我计划的那样专注于此.我正在尝试所有这些不同的技术.
解决方案
我相信，以下查询将帮助您开始，并且它是实现目标的非常有效的方式.
创建它是为了在中央位置维护 SQL Server 的历史信息，并执行以下活动，您必须在相应的脚本块中包含/替换您的表结构
- 创建
temp表 - 通过
Lined Servers(源代码)使用OPENQUERY从多个服务器收集信息并加载到Temp表中. - 在
Temp表上创建索引 - 使用 3 个场景(如脚本中所注释)将数据加载到中央表(目标)中
注意:根据您的场景替换了脚本
<预><代码>开始创建表#SrcTemp( AENAM nvarchar(12),AUTYP nvarchar(2),警告 nvarchar(4),BELNR nvarchar(10),CATSHOURS 十进制(7, 3),CATSQUANTITY 十进制 (18, 3),CHARGE_HOLD nvarchar(24),[计数器] nvarchar(12),ERNAM nvarchar(12),ERSDA nvarchar(8),ERSTM nvarchar(6),HRCOSTASG nvarchar(1),LAEDA nvarchar(8),LSTAR nvarchar(6),LTXA1 nvarchar(40),MANDT nvarchar(3),PERNR nvarchar(8),RAPLZL nvarchar(8),RAUFPL nvarchar(10),REFCOUNTER nvarchar(12),RNPLNR nvarchar(12),SKOSTL nvarchar(10),CATS_STATUS nvarchar(2),SUPP3 nvarchar(10),工作日期 nvarchar(8),ZZOH_ORDER nvarchar(24),APDAT nvarchar(8),APNAM nvarchar(12),LAETM nvarchar(6),APPR_STATUS nvarchar(2));-- 声明@orig_counter nvarchar(12)结尾UPDATE #SrcTemp SET REFCOUNTER = '0' WHERE REFCOUNTER = '' 或 REFCOUNTER 为空；在#SrcTemp ([COUNTER], REFCOUNTER) 上创建聚集索引 CLU_SrvTemp；开始插入#SrcTemp选择AENAM,AUTYP,AWART,BELNR,CATSHOURS,CATSQUANTITY,CHARGE_HOLD,[COUNTER],ERNAM,ERSDA,ERSTM,HRCOSTASG,LAEDA,LSTAR,LTXA1,MANDT,PERNR,RAPLZL,RAUFPL,REFCOUNTER,RNPLNR,SKOSTL,CATS_STATUS,SUPP3,WORKDATE,ZZOH_ORDER,APDAT,APNAM,LAETM,APPR_STATUS从CATSDB；结尾- 开始-- 打开curs- 结尾-- 范围:未更改的记录 ==================================================================================================================================如果存在(选择 *从 (SELECT ROW_NUMBER () OVER (PARTITION BY [COUNTER] ORDER BY COUNTER) AS RN从#SrcTempWHERE REFCOUNTER = '0') 作为 t 其中 t.RN >1)开始RAISERROR ('主键冲突发生在 "UNCHANGED" 记录处理块', 16, 1) 与 NOWAIT;结尾别的开始-- 当 SQL 表中不存在 NON-CHANGED 记录时 --------------------------开始插入温度 ([AENAM],[AUTYP],[AWART],[BELNR],[CATSHOURS],[CATSQUANTITY],[CHARGE_HOLD],[COUNTER],[ERNAM],[ERSDA],[ERSTM],[HRCOSTASG],[LAEDA],[LSTAR],[LTXA1],[MANDT],[PERNR],[RAPLZL],[RAUFPL],[REFCOUNTER],[RNPLNR],[SKOSTL],[CATS_STATUS],[SUPP3],[WORKDATE],[ZZOH_ORDER],[APDAT],[APNAM],[LAETM],[APPR_STATUS])SELECT s.[AENAM], s.[AUTYP], s.[AWART], s.[BELNR], s.[CATSHOURS], s.[CATSQUANTITY], s.[CHARGE_HOLD], s.[计数器], s.[ERNAM], s.[ERSDA], s.[ERSTM], s.[HRCOSTASG], s.[LAEDA], s.[LSTAR], s.[LTXA1], s.[MANDT], s.[PERNR], s.[RAPLZL], s.[RAUFPL], s.[REFCOUNTER], s.[RNPLNR], s.[SKOSTL], s.[CATS_STATUS], s.[SUPP3], s.[WORKDATE], s.[ZZOH_ORDER], s.[APDAT], s.[APNAM], s.[LAETM], s.[APPR_STATUS]FROM #SrcTemp 为 S左加入s.COUNTER = d.COUNTER 上的 TEMP 为 DWHERE (S.REFCOUNTER = '0') 和 D.COUNTER 为 null ；结尾-- 当 SQL 表中存在 NON-CHANGED 记录时 -------------------------开始更新SET [AENAM] = D.AENAM,[AUTYP] = D.AUTYP,[AWART] = D.AWART,[BELNR] = D.BELNR,[CATSHOURS] = D.CATSHOURS,[CATSQUANTITY] = D.CATSQUANTITY,[CHARGE_HOLD] = D.CHARGE_HOLD,[ERNAM] = D.ERNAM,[ERSDA] = D.ERSDA,[ERSTM] = D.ERSTM,[HRCOSTASG] = D.HRCOSTASG,[LAEDA] = D.LAEDA,[LSTAR] = D.LSTAR,[LTXA1] = D.LTXA1,[MANDT] = D.MANDT,[PERNR] = D.PERNR,[RAPLZL] = D.RAPLZL,[RAUFPL] = D.RAUFPL,[REFCOUNTER] = D.REFCOUNTER,[RNPLNR] = D.RNPLNR,[SKOSTL] = D.SKOSTL,[CATS_STATUS] = D.CATS_STATUS,[SUPP3] = D.SUPP3,[工作日期] = D.工作日期,[ZZOH_ORDER] = D.ZZOH_ORDER,[APDAT] = D.APDAT,[APNAM] = D.APNAM,[LAETM] = D.LAETM,[APPR_STATUS] = D.APPR_STATUSFROM #SrcTemp 为 S左加入TEMP as D on (s.COUNTER = d.COUNTER and S.REFCOUNTER = D.REFCOUNTER)WHERE (S.REFCOUNTER = '0') 和 D.COUNTER 不为空结尾结尾-- 范围:已更改的记录 ==================================================================================================================================如果存在(选择 *从 (SELECT ROW_NUMBER () OVER (PARTITION BY [COUNTER], REFCOUNTER ORDER BY [COUNTER]) AS RN从#SrcTempWHERE not REFCOUNTER = '0') 作为 t 其中 t.RN >1)开始RAISERROR ('主键冲突发生在 "CHANGED" 记录处理块', 10, 1) 与 NOWAIT;结尾别的开始-- 当 SQL 表中不存在 CHANGED 记录时 -------------------------------------------开始插入温度 ([AENAM],[AUTYP],[AWART],[BELNR],[CATSHOURS],[CATSQUANTITY],[CHARGE_HOLD],[COUNTER],[ERNAM],[ERSDA],[ERSTM],[HRCOSTASG],[LAEDA],[LSTAR],[LTXA1],[MANDT],[PERNR],[RAPLZL],[RAUFPL],[REFCOUNTER],[RNPLNR],[SKOSTL],[CATS_STATUS],[SUPP3],[WORKDATE],[ZZOH_ORDER],[APDAT],[APNAM],[LAETM],[APPR_STATUS])选择 s.[AENAM], s.[AUTYP], s.[AWART], s.[BELNR], s.[CATSHOURS], s.[CATSQUANTITY], s.[CHARGE_HOLD], s.[计数器], s.[ERNAM], s.[ERSDA], s.[ERSTM], s.[HRCOSTASG], s.[LAEDA], s.[LSTAR], s.[LTXA1], s.[MANDT], s.[PERNR], s.[RAPLZL], s.[RAUFPL], s.[REFCOUNTER], s.[RNPLNR], s.[SKOSTL], s.[CATS_STATUS], s.[SUPP3], s.[WORKDATE], s.[ZZOH_ORDER], s.[APDAT], s.[APNAM], s.[LAETM], s.[APPR_STATUS]FROM #SrcTemp 为 S左加入在 s.COUNTER = d.COUNTER 和 S.REFCOUNTER = D.REFCOUNTER 上，TEMP 为 DWHERE(不是 S.REFCOUNTER = '0')并且 D.COUNTER 为空结尾-- 当 SQL 表中存在 NON-CHANGED 记录时 -------------------------开始更新SET [AENAM] = D.AENAM,[AUTYP] = D.AUTYP,[AWART] = D.AWART,[BELNR] = D.BELNR,[CATSHOURS] = D.CATSHOURS,[CATSQUANTITY] = D.CATSQUANTITY,[CHARGE_HOLD] = D.CHARGE_HOLD,[ERNAM] = D.ERNAM,[ERSDA] = D.ERSDA,[ERSTM] = D.ERSTM,[HRCOSTASG] = D.HRCOSTASG,[LAEDA] = D.LAEDA,[LSTAR] = D.LSTAR,[LTXA1] = D.LTXA1,[MANDT] = D.MANDT,[PERNR] = D.PERNR,[RAPLZL] = D.RAPLZL,[RAUFPL] = D.RAUFPL,[REFCOUNTER] = D.REFCOUNTER,[RNPLNR] = D.RNPLNR,[SKOSTL] = D.SKOSTL,[CATS_STATUS] = D.CATS_STATUS,[SUPP3] = D.SUPP3,[工作日期] = D.工作日期,[ZZOH_ORDER] = D.ZZOH_ORDER,[APDAT] = D.APDAT,[APNAM] = D.APNAM,[LAETM] = D.LAETM,[APPR_STATUS] = D.APPR_STATUSFROM #SrcTemp 为 S左加入在 s.COUNTER = d.COUNTER 和 S.REFCOUNTER = D.REFCOUNTER 上，TEMP 为 DWHERE (not S.REFCOUNTER = '0' ) 和 D.COUNTER 不为空结尾结尾删除表#SrcTemp;I am working with SAP Timesheet data, so there are millions of rows. What I am trying to do is select the data from the SAP table and insert it into a table on MS SQL Server.
So I want to insert the original record, then if an update to the original record happens, which is in the form of a new SAP record with a refcounter, I want to find the original record in my table and update it, keeping the original counter value.
So I have done this successfully with a cursor (I know not the best), but with millions of records, I am wondering if there is a faster way, because I am on day 4 of my cursor running. Is there a better way then what I have below:
BEGIN
CREATE TABLE CATSDB
(
[COUNTER] nvarchar(12),
REFCOUNTER nvarchar(12),
PERNR nvarchar(8),
WORKDATE nvarchar(8),
CATSHOURS decimal(7, 3),
APDAT nvarchar(8),
LAETM nvarchar(6),
CATS_STATUS nvarchar(2),
APPR_STATUS nvarchar(2)
)
INSERT INTO CATSDB
(
[COUNTER],REFCOUNTER,PERNR,WORKDATE,CATSHOURS,APDAT,LAETM,CATS_STATUS,APPR_STATUS
)
VALUES
('000421692670',NULL,'00000071','20190114','6.00','20190204','174541','30','30'),
('000421692671',NULL,'00000071','20190114','3.00','20190204','174541','30','30'),
('000421692672',NULL,'00000071','20190115','6.00','00000000','000000','60','20'),
('000421692673',NULL,'00000071','20190115','3.00','00000000','000000','60','20'),
('000421692712','000421692672','00000071','20190115','0.00','20190115','111007','30','30'),
('000421692713','000421692673','00000071','20190115','0.00','20190115','111007','30','30'),
('000429718015',NULL,'00000072','20190313','7.00','00000000','000000','60','20'),
('000429718016',NULL,'00000072','20190313','1.50','20190315','164659','30','30'),
('000429718017',NULL,'00000072','20190313','1.00','20190315','164659','30','30'),
('000430154143',NULL,'00000072','20190313','2.00','00000000','000000','60','20'),
('000430154142','000429718015','00000072','20190313','5.00','00000000','000000','60','20'),
('000430154928','000430154142','00000072','20190313','4.50','20190315','164659','30','30'),
('000430154929','000430154143','00000072','20190313','2.50','20190315','164659','30','30'),
('000429774620',NULL,'00000152','20190314','1.00','00000000','000000','60','20'),
('000429774619',NULL,'00000152','20190314','1.00','00000000','000000','60','20'),
('000429802106','000429774620','00000152','20190314','2.00','00000000','000000','60','20'),
('000429802105','000429774619','00000152','20190314','3.00','00000000','000000','60','20'),
('000429840242','000429802106','00000152','20190314','4.00','20190315','143857','30','30'),
('000429840241','000429802105','00000152','20190314','5.00','20190315','143857','30','30')
CREATE TABLE [TBL_COUNTER]
(
[COUNTER] [varchar](12) NOT NULL,
[REFCOUNTER] [varchar](12) NULL
)
CREATE TABLE TEMP
(
[COUNTER] [nvarchar](12) NOT NULL,
[REFCOUNTER] [nvarchar](12) NULL,
[PERNR] [nvarchar](8) NULL,
[WORKDATE] [nvarchar](8) NULL,
[CATSHOURS] [decimal](7, 3) NULL,
[APDAT] [nvarchar](8) NULL,
[LAETM] [nvarchar](6) NULL,
[CATS_STATUS] [nvarchar](2) NULL,
[APPR_STATUS] [nvarchar](2) NULL
)
END
BEGIN
DECLARE @COUNTER nvarchar(12),
@REFCOUNTER nvarchar(12),
@PERNR nvarchar(8),
@WORKDATE nvarchar(8),
@CATSHOURS decimal(7, 3),
@APDAT nvarchar(8),
@LAETM nvarchar(6),
@CATS_STATUS nvarchar(2),
@APPR_STATUS nvarchar(2)
DECLARE @orig_counter nvarchar(12)
END
BEGIN
DECLARE curs CURSOR FOR
SELECT
[COUNTER],
REFCOUNTER,
PERNR,
WORKDATE,
CATSHOURS,
APDAT,
LAETM,
CATS_STATUS,
APPR_STATUS
FROM
CATSDB
END
BEGIN
OPEN curs
END
BEGIN
FETCH NEXT FROM curs INTO
@COUNTER,
@REFCOUNTER,
@PERNR,
@WORKDATE,
@CATSHOURS,
@APDAT,
@LAETM,
@CATS_STATUS,
@APPR_STATUS
END
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
IF NOT EXISTS (SELECT * FROM TBL_COUNTER WHERE [COUNTER] = @COUNTER)
BEGIN
INSERT INTO TBL_COUNTER
([COUNTER]
,REFCOUNTER)
VALUES
(@COUNTER
,@REFCOUNTER)
END
END
BEGIN
IF NOT EXISTS (SELECT * FROM TEMP WHERE [COUNTER] = @COUNTER)
BEGIN
--If REFCOUNTER is populated, get the original COUNTER value, then update that row with the new values. Otherwise insert new record
IF @REFCOUNTER <> '' AND @REFCOUNTER IS NOT NULL
BEGIN
BEGIN
WITH n([COUNTER], REFCOUNTER) AS
(
SELECT
cnt.[COUNTER],
cnt.REFCOUNTER
FROM
TBL_COUNTER cnt
WHERE
cnt.[COUNTER] = @REFCOUNTER
UNION ALL
SELECT
nplus1.[COUNTER],
nplus1.REFCOUNTER
FROM
TBL_COUNTER as nplus1,
n
WHERE
n.[COUNTER] = nplus1.REFCOUNTER
)
SELECT @orig_counter = [COUNTER] FROM n WHERE REFCOUNTER = '' OR REFCOUNTER IS NULL
END
BEGIN
UPDATE TEMP
SET
[REFCOUNTER] = @REFCOUNTER
,[PERNR] = @PERNR
,[WORKDATE] = @WORKDATE
,[CATSHOURS] = @CATSHOURS
,[APDAT] = @APDAT
,[LAETM] = @LAETM
,[CATS_STATUS] = @CATS_STATUS
,[APPR_STATUS] = @APPR_STATUS
WHERE [COUNTER] = @orig_counter
END
END
ELSE
BEGIN
INSERT INTO TEMP
([COUNTER]
,[REFCOUNTER]
,[PERNR]
,[WORKDATE]
,[CATSHOURS]
,[APDAT]
,[LAETM]
,[CATS_STATUS]
,[APPR_STATUS])
VALUES
(@COUNTER
,@REFCOUNTER
,@PERNR
,@WORKDATE
,@CATSHOURS
,@APDAT
,@LAETM
,@CATS_STATUS
,@APPR_STATUS)
END
END
FETCH NEXT FROM curs INTO
@COUNTER,
@REFCOUNTER,
@PERNR,
@WORKDATE,
@CATSHOURS,
@APDAT,
@LAETM,
@CATS_STATUS,
@APPR_STATUS
END
END
END
BEGIN
CLOSE curs
DEALLOCATE curs
END
I shortened it and created the tables for you all to be able to see what is going on. The expected result is
+--------------+--------------+----------+----------+-----------+----------+--------+-------------+-------------+
| COUNTER | REFCOUNTER | PERNR | WORKDATE | CATSHOURS | APDAT | LAETM | CATS_STATUS | APPR_STATUS |
+--------------+--------------+----------+----------+-----------+----------+--------+-------------+-------------+
| 000421692670 | NULL | 00000071 | 20190114 | 6.00 | 20190204 | 174541 | 30 | 30 |
| 000421692671 | NULL | 00000071 | 20190114 | 3.00 | 20190204 | 174541 | 30 | 30 |
| 000421692672 | 000421692672 | 00000071 | 20190115 | 0.00 | 20190115 | 111007 | 30 | 30 |
| 000421692673 | 000421692673 | 00000071 | 20190115 | 0.00 | 20190115 | 111007 | 30 | 30 |
| 000429718015 | 000430154142 | 00000072 | 20190313 | 4.50 | 20190315 | 164659 | 30 | 30 |
| 000429718016 | NULL | 00000072 | 20190313 | 1.50 | 20190315 | 164659 | 30 | 30 |
| 000429718017 | NULL | 00000072 | 20190313 | 1.0 | 20190315 | 164659 | 30 | 30 |
| 000430154143 | 000430154143 | 00000072 | 20190313 | 2.50 | 20190315 | 164659 | 30 | 30 |
| 000429774620 | 000429774620 | 00000152 | 20190314 | 2.00 | 00000000 | 000000 | 60 | 20 |
| 000429774619 | 000429802105 | 00000152 | 20190314 | 5.00 | 20190315 | 143857 | 30 | 30 |
+--------------+--------------+----------+----------+-----------+----------+--------+-------------+-------------+
I need to add to this. So there is two phases to this. The first phase is I will pull all the data from 2019 for an initial load of my table. Then on a weekly basis, I will pull the data from the origin source for new records and changed records from the last time i ran it. So I will not have the full chain every week. There needs to be a way to get back to the original counter value, without the full dataset, which is why i had the counter table. I apologize for not being more clear. I am swamped with work and havent been able to focus on this as much as I planned. I am trying all these different techniques.
解决方案
I believe, following query would help you to start with and it's much efficient way to approach you goal.
It was created to maintain historical info of SQL Servers in central location, and performs following activities, you have to include/replace your table structures in respective blocks of script
- Creates
temptable - Collects the information from multiple servers using
OPENQUERYviaLined Servers(source) and loads intoTempTable. - Creates Indexes on
Temptables - Loads the data into Central Table (destination) with 3 scenarios (as commented in script)
Note: Replaced the script as per your scenario
BEGIN
Create Table #SrcTemp
( AENAM nvarchar(12),
AUTYP nvarchar(2),
AWART nvarchar(4),
BELNR nvarchar(10),
CATSHOURS decimal(7, 3),
CATSQUANTITY decimal(18, 3),
CHARGE_HOLD nvarchar(24),
[COUNTER] nvarchar(12),
ERNAM nvarchar(12),
ERSDA nvarchar(8),
ERSTM nvarchar(6),
HRCOSTASG nvarchar(1),
LAEDA nvarchar(8),
LSTAR nvarchar(6),
LTXA1 nvarchar(40),
MANDT nvarchar(3),
PERNR nvarchar(8),
RAPLZL nvarchar(8),
RAUFPL nvarchar(10),
REFCOUNTER nvarchar(12),
RNPLNR nvarchar(12),
SKOSTL nvarchar(10),
CATS_STATUS nvarchar(2),
SUPP3 nvarchar(10),
WORKDATE nvarchar(8),
ZZOH_ORDER nvarchar(24),
APDAT nvarchar(8),
APNAM nvarchar(12),
LAETM nvarchar(6),
APPR_STATUS nvarchar(2)
);
-- DECLARE @orig_counter nvarchar(12)
END
UPDATE #SrcTemp SET REFCOUNTER = '0' WHERE REFCOUNTER = '' or REFCOUNTER is null;
CREATE Clustered Index CLU_SrvTemp on #SrcTemp ([COUNTER], REFCOUNTER);
BEGIN
INSERT INTO #SrcTemp
SELECT
AENAM,AUTYP,AWART,BELNR,CATSHOURS,CATSQUANTITY,CHARGE_HOLD,[COUNTER],ERNAM,ERSDA,ERSTM,HRCOSTASG,LAEDA,LSTAR,LTXA1,MANDT,
PERNR,RAPLZL,RAUFPL,REFCOUNTER,RNPLNR,SKOSTL,CATS_STATUS,SUPP3,WORKDATE,ZZOH_ORDER,APDAT,APNAM,LAETM,APPR_STATUS
FROM
CATSDB;
END
--BEGIN
-- OPEN curs
--END
-- Scope: UNCHANGED Records ==================================================================================================================================
IF EXISTS
(select *
from (
SELECT ROW_NUMBER () OVER (PARTITION BY [COUNTER] ORDER BY COUNTER) AS RN
FROM #SrcTemp
WHERE REFCOUNTER = '0'
) as t where t.RN > 1
)
BEGIN
RAISERROR ('Primary key violation occurred in "UNCHANGED" records processing block', 16, 1) with NOWAIT;
END
ELSE
BEGIN
-- When NON-CHANGED Records NOT Existed in SQL table -------------------------------------------
BEGIN
INSERT INTO TEMP ([AENAM],[AUTYP],[AWART],[BELNR],[CATSHOURS],[CATSQUANTITY],[CHARGE_HOLD],[COUNTER],[ERNAM]
,[ERSDA],[ERSTM],[HRCOSTASG],[LAEDA],[LSTAR],[LTXA1],[MANDT],[PERNR],[RAPLZL],[RAUFPL]
,[REFCOUNTER],[RNPLNR],[SKOSTL],[CATS_STATUS],[SUPP3],[WORKDATE],[ZZOH_ORDER],[APDAT],[APNAM]
,[LAETM],[APPR_STATUS]
)
SELECT s.[AENAM], s.[AUTYP], s.[AWART], s.[BELNR], s.[CATSHOURS], s.[CATSQUANTITY], s.[CHARGE_HOLD], s.[COUNTER], s.[ERNAM]
, s.[ERSDA], s.[ERSTM], s.[HRCOSTASG], s.[LAEDA], s.[LSTAR], s.[LTXA1], s.[MANDT], s.[PERNR], s.[RAPLZL], s.[RAUFPL]
, s.[REFCOUNTER], s.[RNPLNR], s.[SKOSTL], s.[CATS_STATUS], s.[SUPP3], s.[WORKDATE], s.[ZZOH_ORDER], s.[APDAT], s.[APNAM]
, s.[LAETM], s.[APPR_STATUS]
FROM #SrcTemp as S
LEFT JOIN
TEMP as D on s.COUNTER = d.COUNTER
WHERE (S.REFCOUNTER = '0') and D.COUNTER is null ;
END
-- When NON-CHANGED Records Existed in SQL table -------------------------------------------
BEGIN
UPDATE S
SET [AENAM] = D.AENAM
,[AUTYP] = D.AUTYP
,[AWART] = D.AWART
,[BELNR] = D.BELNR
,[CATSHOURS] = D.CATSHOURS
,[CATSQUANTITY] = D.CATSQUANTITY
,[CHARGE_HOLD] = D.CHARGE_HOLD
,[ERNAM] = D.ERNAM
,[ERSDA] = D.ERSDA
,[ERSTM] = D.ERSTM
,[HRCOSTASG] = D.HRCOSTASG
,[LAEDA] = D.LAEDA
,[LSTAR] = D.LSTAR
,[LTXA1] = D.LTXA1
,[MANDT] = D.MANDT
,[PERNR] = D.PERNR
,[RAPLZL] = D.RAPLZL
,[RAUFPL] = D.RAUFPL
,[REFCOUNTER] = D.REFCOUNTER
,[RNPLNR] = D.RNPLNR
,[SKOSTL] = D.SKOSTL
,[CATS_STATUS] = D.CATS_STATUS
,[SUPP3] = D.SUPP3
,[WORKDATE] = D.WORKDATE
,[ZZOH_ORDER] = D.ZZOH_ORDER
,[APDAT] = D.APDAT
,[APNAM] = D.APNAM
,[LAETM] = D.LAETM
,[APPR_STATUS] = D.APPR_STATUS
FROM #SrcTemp as S
LEFT JOIN
TEMP as D on (s.COUNTER = d.COUNTER and S.REFCOUNTER = D.REFCOUNTER)
WHERE (S.REFCOUNTER = '0') and D.COUNTER is NOT null
END
END
-- Scope: CHANGED Records ==================================================================================================================================
IF EXISTS
(select *
from (
SELECT ROW_NUMBER () OVER (PARTITION BY [COUNTER], REFCOUNTER ORDER BY [COUNTER]) AS RN
FROM #SrcTemp
WHERE not REFCOUNTER = '0'
) as t where t.RN > 1
)
BEGIN
RAISERROR ('Primary key violation occurred in "CHANGED" records processing block', 10, 1) with NOWAIT;
END
ELSE
BEGIN
-- When CHANGED Records NOT Existed in SQL table -------------------------------------------
BEGIN
INSERT INTO TEMP ([AENAM],[AUTYP],[AWART],[BELNR],[CATSHOURS],[CATSQUANTITY],[CHARGE_HOLD],[COUNTER],[ERNAM]
,[ERSDA],[ERSTM],[HRCOSTASG],[LAEDA],[LSTAR],[LTXA1],[MANDT],[PERNR],[RAPLZL],[RAUFPL]
,[REFCOUNTER],[RNPLNR],[SKOSTL],[CATS_STATUS],[SUPP3],[WORKDATE],[ZZOH_ORDER],[APDAT],[APNAM]
,[LAETM],[APPR_STATUS]
)
SELECT s.[AENAM], s.[AUTYP], s.[AWART], s.[BELNR], s.[CATSHOURS], s.[CATSQUANTITY], s.[CHARGE_HOLD], s.[COUNTER], s.[ERNAM]
, s.[ERSDA], s.[ERSTM], s.[HRCOSTASG], s.[LAEDA], s.[LSTAR], s.[LTXA1], s.[MANDT], s.[PERNR], s.[RAPLZL], s.[RAUFPL]
, s.[REFCOUNTER], s.[RNPLNR], s.[SKOSTL], s.[CATS_STATUS], s.[SUPP3], s.[WORKDATE], s.[ZZOH_ORDER], s.[APDAT], s.[APNAM]
, s.[LAETM], s.[APPR_STATUS]
FROM #SrcTemp as S
LEFT JOIN
TEMP as D on s.COUNTER = d.COUNTER and S.REFCOUNTER = D.REFCOUNTER
WHERE (not S.REFCOUNTER = '0') and D.COUNTER is null
END
-- When NON-CHANGED Records Existed in SQL table -------------------------------------------
BEGIN
UPDATE S
SET [AENAM] = D.AENAM
,[AUTYP] = D.AUTYP
,[AWART] = D.AWART
,[BELNR] = D.BELNR
,[CATSHOURS] = D.CATSHOURS
,[CATSQUANTITY] = D.CATSQUANTITY
,[CHARGE_HOLD] = D.CHARGE_HOLD
,[ERNAM] = D.ERNAM
,[ERSDA] = D.ERSDA
,[ERSTM] = D.ERSTM
,[HRCOSTASG] = D.HRCOSTASG
,[LAEDA] = D.LAEDA
,[LSTAR] = D.LSTAR
,[LTXA1] = D.LTXA1
,[MANDT] = D.MANDT
,[PERNR] = D.PERNR
,[RAPLZL] = D.RAPLZL
,[RAUFPL] = D.RAUFPL
,[REFCOUNTER] = D.REFCOUNTER
,[RNPLNR] = D.RNPLNR
,[SKOSTL] = D.SKOSTL
,[CATS_STATUS] = D.CATS_STATUS
,[SUPP3] = D.SUPP3
,[WORKDATE] = D.WORKDATE
,[ZZOH_ORDER] = D.ZZOH_ORDER
,[APDAT] = D.APDAT
,[APNAM] = D.APNAM
,[LAETM] = D.LAETM
,[APPR_STATUS] = D.APPR_STATUS
FROM #SrcTemp as S
LEFT JOIN
TEMP as D on s.COUNTER = d.COUNTER and S.REFCOUNTER = D.REFCOUNTER
WHERE (not S.REFCOUNTER = '0' ) and D.COUNTER is NOT null
END
END
Drop table #SrcTemp;
这篇关于SQL Server 迭代数百万行的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
