CODEWITHSUNDEEP
sql-servert-sqlvariablesbulkinsert
Carter Wetherington
Carter Wetherington

Reputation: 13

Bulk INSERT Tbl From @path

I would like to do a bulk insert using a loop that will cycle through several hundred files. But I can't seem to use a variable as the FROM path. Can I use FROM @PATH3 or is there another way to BULK INSERT many text files? Thank you

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

Upvotes: 0

Views: 328

Answers (2)

Carter Wetherington
Carter Wetherington

Reputation: 13

Thank you so much @Jaime! This is what I ended on with the While loop.

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

Upvotes: 0

James
James

Reputation: 3015

Try with dynamic sql, something like this:

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

Upvotes: 1

Related Questions