Bulk insert parameter specification 1

[MarkButler]

I am having a problem supplying a file name to a bulk insert command. In the following I am trying to build the file name using a directory which may change between servers and the fixed file name.

DECLARE @updates_dir varchar (500), @provlist varchar(500)
SET @updates_dir = 'd:\updates\'
SET @provlist = @updates_dir + 'provlist.seq'
BULK INSERT T1_WORK 
   FROM @provlist
   WITH
      (
      DATAFILETYPE = 'char',
      ROWTERMINATOR = '\n'
      )

Trying to run it as above I get the following:
Line 5: Incorrect syntax near '@provlist'.

Running it as follows works as advertised.

BULK INSERT T1_WORK 
   FROM 'D:\UPDATES\PROVLIST.SEQ'
   WITH
      (
      DATAFILETYPE = 'char',
      ROWTERMINATOR = '\n'
      )

How can I build the input file name and supply it to the command?

TIA
Mark

 

[MartinCroft]

Hi you could build the BULK INSERT dynamically and use
exec to execute, Ive added a couple extra lines to your code, not been able to test as it would require creating tables and load file, but this gets as far as saying it can't find specified file, a minor tweek if needed should sort it.

DECLARE @updates_dir varchar (500), @provlist varchar(500),@stText varchar(1000)
SET @updates_dir = 'd:\updates\'
SET @provlist = @updates_dir + 'provlist.seq'
SET @stText='

BULK INSERT T1_WORK
FROM '''+@provlist+'''
WITH
(
DATAFILETYPE = ''char'',
ROWTERMINATOR = ''\n''
)

'

print @sttext

[COLOR=green ]exec (@sttext)[/color]


ive highlighted the changes the trick to watch it the double '' around the original ' if you comment out the exec you'll see the print statements output, its just basically building up the command you require and then executing it

hope this is of help

mysticmart

 

[MarkButler]

Thanx mysticmart. That did the trick but the "CHAR" with double quotes did not work (was not recognized). Because I was using defaults and did not need it nor the rowterminator I just omitted them and all was well. I think what would have worked would have been three single quotes when one was needed.

Again thanx, you got a star for that one ;-)