如何使用循环将数据增量更新到另一个表中？

mohan111 发表于 Dev

mohan111

我有一个查询是这样的：

CREATE TABLE #Employee
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Basavaraj Biradar', 0),
        (2, 'Shree Biradar', 0),
        (3, 'Kalpana Biradar', 0)
GO

DECLARE @LoopCounter INT = 1, @MaxEmployeeId INT = 10 , 
        @EmployeeName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = (Name)
   FROM #Employee WHERE Id = @LoopCounter


   SET @LoopCounter  = @LoopCounter  + 1     
    PRINT @EmployeeName     
END

给出结果：

Basavaraj Biradar
Shree Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar

预期产量：

1 Basavaraj Biradar
2 Shree Biradar
3 Kalpana Biradar
4 Basavaraj Biradar
5 Shree Biradar
6 Kalpana Biradar
7 Basavaraj Biradar
8 Shree Biradar
9 Kalpana Biradar
10 Basavaraj Biradar
11 Shree Biradar
12 Kalpana Biradar

菲利克斯·帕米坦（Felix Pamittan）

无需WHILE循环或即可完成此操作CURSOR。您只需要一个理货单：

SQL小提琴

DECLARE @MaxEmployeeId INT = 10;

DECLARE @N INT; 
SELECT @N = CEILING(@MaxEmployeeId/(COUNT(*)*1.0)) FROM #Employee;

;WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
CteTally(N) AS(
    SELECT TOP(@N) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteFinal AS(
    SELECT *, Rn = ROW_NUMBER() OVER(ORDER BY N, Id) 
    FROM #Employee e
    CROSS JOIN CteTally t
)
SELECT Rn, Name FROM CteFinal WHERE Rn <= @MaxEmployeeId ORDER BY Rn

如果只是重复记录的次数：

SQL小提琴

DECLARE @RepeatTimes INT = 4
;WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
CteTally(N) AS(
    SELECT TOP(@RepeatTimes) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT 
    Rn = ROW_NUMBER() OVER(ORDER BY N, Id), e.Name
FROM Employee e
CROSS JOIN CteTally t
ORDER BY Rn

结果：

| Rn |              Name |
|----|-------------------|
|  1 | Basavaraj Biradar |
|  2 |     Shree Biradar |
|  3 |   Kalpana Biradar |
|  4 | Basavaraj Biradar |
|  5 |     Shree Biradar |
|  6 |   Kalpana Biradar |
|  7 | Basavaraj Biradar |
|  8 |     Shree Biradar |
|  9 |   Kalpana Biradar |
| 10 | Basavaraj Biradar |
| 11 |     Shree Biradar |
| 12 |   Kalpana Biradar |

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。