动态插入循环

Question

据我所知，下面的代码：

declare @csvnmbr nvarchar(3)
declare @sql nvarchar(400)
set @csvnmbr = 1
set @sql = 'BULK INSERT #tablename FROM ''C:\TEMP\'+@csvnmbr+'.csv''
            WITH (FIELDTERMINATOR = '','')'
while @csvnmbr < 4
begin
print @sql
set @csvnmbr = @csvnmbr + 1
end
GO

应打印：

BULK INSERT #tablename FROM 'C:\TEMP\1.csv' WITH (FIELDTERMINATOR = ',')

BULK INSERT #tablename FROM 'C:\TEMP\2.csv' WITH (FIELDTERMINATOR = ',')

BULK INSERT #tablename FROM 'C:\TEMP\3.csv' WITH (FIELDTERMINATOR = ',')

但它不会，它会打印：

BULK INSERT #tablename FROM 'C:\TEMP\1.csv' WITH (FIELDTERMINATOR = ',')

BULK INSERT #tablename FROM 'C:\TEMP\1.csv' WITH (FIELDTERMINATOR = ',')

BULK INSERT #tablename FROM 'C:\TEMP\1.csv' WITH (FIELDTERMINATOR = ',')

有人知道为什么循环不打印数字序列而只打印第一个csv数字吗？

但是，如果我这样做：

declare @csvnmbr nvarchar(3)
declare @sql nvarchar(400)
set @csvnmbr = 1
set @sql = 'HI'
while @csvnmbr < 4
begin
print @sql
print @csvnmbr
set @csvnmbr = @csvnmbr + 1
end
GO

显然没有必要在循环中编写@sql。你知道为什么@sql语句是动态的意味着它每次都需要重置吗？

Answer 1

因为它在循环开始之前设置一次。你需要在循环中设置@sql：

DECLARE @csvnmbr  INT = 1
        ,@sql     NVARCHAR(MAX);

WHILE @csvnmbr < 4
BEGIN
   SET @sql = N'BULK INSERT #tablename FROM ''C:\TEMP\'' +
               CAST(@csvnmbr AS NVARCHAR(10)) + 
               '.csv'' WITH (FIELDTERMINATOR = '','')';
   PRINT @sql;
   SET @csvnmbr += 1;
END

关于你的问题，为什么它需要在循环中：

declare @csvnmbr nvarchar(3)
declare @sql nvarchar(400)
set @csvnmbr = 1

/* At this point @sql if fixed string, during concatenation @csvnmbr was
   replaced by its actual value */
set @sql = 'BULK INSERT #tablename FROM ''C:\TEMP\'+@csvnmbr+'.csv''
            WITH (FIELDTERMINATOR = '','')'


while @csvnmbr < 4
begin
print @sql  /* Here sql will be always the same you need to set it again */
set @csvnmbr = @csvnmbr + 1
end

你想要达到的目的是通过引用传递字符串。你可以像这样模仿它：

DECLARE @csvnmbr NVARCHAR(3) = '1'
        ,@sql NVARCHAR(MAX);

SET @sql = 
 N'BULK INSERT #tablename FROM ''C:\TEMP\@csvnmbr.csv'' WITH (FIELDTERMINATOR = '','')';

WHILE @csvnmbr < 4
BEGIN
   /* Warning!!! This will execute code in @sql*/
   EXEC [dbo].[sp_executesql]
           @sql,
           N'@csvnmbr nvarchar(3)',
           @cvnmbr;                  /* Will substitute @csvnmbr with passed value */

   SET @csvnmbr += 1;
END

Answer 2

将您的语句放在while循环中

declare @csvnmbr nvarchar(3)
declare @sql nvarchar(400)
set @csvnmbr = 1

while @csvnmbr < 4
begin
 set @sql = 'BULK INSERT #tablename FROM ''C:\TEMP\'+@csvnmbr+'.csv''
            WITH (FIELDTERMINATOR = '','')'
print @sql
set @csvnmbr = @csvnmbr + 1
end
GO

while循环迭代发生在while循环条件begin和end块之间。

WHILE (Some Condition)   --<-- first the condition is checked
  BEGIN
                         --<-- if the while condition is evaluated to true
                            -- code between the BEGIN and END is executed
       -- it keeps getting executing until while condition returns false
  END

Answer 3

您需要在每次迭代中填写@sql：

DECLARE @csvnmbr NVARCHAR(3);
DECLARE @sql NVARCHAR(400);
SET @csvnmbr = 1;

SET @sql = 'BULK INSERT #tablename FROM ''C:\TEMP\' + @csvnmbr + '.csv''
        WITH (FIELDTERMINATOR = '','')';

WHILE @csvnmbr < 4

BEGIN

    PRINT @sql;

    SET @csvnmbr = @csvnmbr + 1;

SET @sql = 'BULK INSERT #tablename FROM ''C:\TEMP\' + @csvnmbr + '.csv''
         WITH (FIELDTERMINATOR = '','')';    

END;

GO