没有记录插入函数来生成多个新记录的记录

Question

首次使用SQL中的WALE函数。我经历了很多帖子，试图弄清楚我做错了什么，但尚未找到。

DECLARE @BeginTagNo INT
        SELECT @BeginTagNo = (SELECT MIN(BEGINTAGNO) FROM dbo.INLoadTagReconciliation WHERE BEGINTAGNO <> 0 AND TYPE = 1 AND TRACTCODE = '00099-023-F')
DECLARE @EndTagNo INT
        SELECT @EndTagNo = (SELECT MAX(ENDTAGNO) FROM dbo.INLoadTagReconciliation WHERE ENDTAGNO <> 0 AND TYPE = 1 AND TRACTCODE = '00099-023-F')
DECLARE @id INT
        SELECT @BeginTagNo = (SELECT MIN(BEGINTAGNO) FROM dbo.INLoadTagReconciliation WHERE BEGINTAGNO <> 0 AND TYPE = 1 AND TRACTCODE = '00099-023-F')     
WHILE @id >= @BeginTagNo and @id <= @EndTagNo
BEGIN
    insert INTO workINLoadTagReconciliation values(@id,null,null)
    select @id = @id+1
END

当我运行上面时，什么都没输入表中。当您运行此操作时：

SELECT @BeginTagNo = (SELECT MIN(BEGINTAGNO) FROM dbo.INLoadTagReconciliation WHERE BEGINTAGNO <> 0 AND TYPE = 1 AND TRACTCODE = '00099-023-F')

您被返回2021901。

运行此操作时：

SELECT @EndTagNo = (SELECT MAX(ENDTAGNO) FROM dbo.INLoadTagReconciliation WHERE ENDTAGNO <> 0 AND TYPE = 1 AND TRACTCODE = '00099-023-F')

您将返回2022600。@ID变量与@begintagno相同。我希望从2021901到2022600的699行中产生一张桌子。任何人都可以阐明我的愚蠢。

Answer 1

忘记循环。尝试;

INSERT INTO workINLoadTagReconciliation 
SELECT BEGINTAGNO, NULL, NULL
FROM dbo.INLoadTagReconciliation
WHERE BEGINTAGNO >= 
    (SELECT MIN(BEGINTAGNO) FROM dbo.INLoadTagReconciliation 
      WHERE BEGINTAGNO <> 0 AND TYPE = 1 AND TRACTCODE = '00099-023-F')
AND BEGINTAGNO <= 
    (SELECT MAX(ENDTAGNO) FROM dbo.INLoadTagReconciliation 
      WHERE ENDTAGNO <> 0 AND TYPE = 1 AND TRACTCODE = '00099-023-F')