从@path大容量插入Tbl

Question

我想使用一个循环来执行批量插入，该循环将遍历数百个文件。但我似乎不能用变量作为FROM路径。我可以使用FROM @PATH3 3，还是有其他方式批量插入多个文本文件？谢谢

Begin
declare @days as varchar(2) = '06'
declare @path1 varchar(28) ='E:\WorkingTkr Data\_GDPD_02-'
declare @path2 varchar(9) ='-2020.trk'
declare @path3 varchar(40) = @path1+@days+@path2
--print (@path3)
bulk insert [dbo].[GDPD_Trk]
from @path3
WITH 
  (
    FIELDTERMINATOR = ' ', 
    ROWTERMINATOR = '\n' 
  )
  End

Answer 1

尝试使用动态sql，如下所示：

BEGIN
  declare @days as varchar(2) = '06'
  declare @path1 varchar(28) ='E:\WorkingTkr Data\_GDPD_02-'
  declare @path2 varchar(9) ='-2020.trk'
  declare @path3 varchar(40) = @path1+@days+@path2
          
  declare @sqlBulk varchar(max) = 
'bulk insert [dbo].[GDPD_Trk] ' + char(13) +
'from ''' + @path3 + '''' + char(13) + 
'with' + char(13) +
'(' + char(13) +
'  FIELDTERMINATOR = '' '',' + char(13) +  
'  ROWTERMINATOR = ''\n''' + char(13) +
')'
        
  exec (@sqlBulk)
END

Answer 2

太谢谢你了，詹姆！这就是我用While循环结束的。

declare @Count INT = 1
  declare @days as varchar(2)
  declare @path1 varchar(28) ='E:\WorkingTkr_Data\_JCSO_06-'
  declare @path2 varchar(9) ='-2021.trk'
 WHILE (@Count <32)
 BEGIN
 IF @Count<10
Begin
  set @days = '0'+convert(varchar,@Count)
  SET @Count = @Count + 1
End
Else
begin
  set @days = convert(varchar,@Count)
  SET @Count = @Count + 1
end
  declare @path3 varchar(40) = @path1+@days+@path2
          
  declare @sqlBulk varchar(max) = 
'bulk insert [dbo].[Trk_HeatMap] ' + char(13) +
'from ''' + @path3 + '''' + char(13) + 
'with' + char(13) +
'(' + char(13) +
'  FIELDTERMINATOR = '' '',' + char(13) +  
'  ROWTERMINATOR = ''\n''' + char(13) +
')'
        
  exec (@sqlBulk)
END