Bulk insert using stored procedure

Question

I have a query which is working fine:

BULK INSERT ZIPCodes  FROM  'e:\5-digit Commercial.csv'  WITH  (       FIRSTROW = 2 ,     FIELDTERMINATOR = ',',      ROWTERMINATOR = '\n'  ) 

but now I want to create a stored procedure for it.

I have written below code to make its stored procedure:

create proc dbo.InsertZipCode @filepath varchar(500)='e:\5-digit Commercial.csv' as begin BULK INSERT ZIPCodes  FROM  @filepath  WITH  (       FIRSTROW = 2 ,     FIELDTERMINATOR = ',',      ROWTERMINATOR = '\n'  ) end 

but its showing error:

Msg 102, Level 15, State 1, Procedure InsertZipCode, Line 6 Incorrect syntax near '@filepath'.

Msg 319, Level 15, State 1, Procedure InsertZipCode, Line 7 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Please tell me what I am doing wrong and what I can do to make it work in stored procedure.

Thanks

Answer 1

There's nothing wrong with your stored procedure code - the point is: the BULK INSERT command cannot accept a file name as a variable.

This does work:

BULK INSERT ZIPCodes  FROM  'e:\5-digit Commercial.csv'  WITH  

but this never works - within a stored proc or not:

DECLARE @filename VARCHAR(255) SET @filename = 'e:\5-digit Commercial.csv'   BULK INSERT ZIPCodes  FROM @filename WITH  

So you just cannot do it this way, unfortunately. You could consider building up your BULK INSERT statement as a string (with a fixed file name) and then execute it as dynamic SQL - but I don't really see any other solution.

DECLARE @filepath nvarchar(500) SET @filepath = N'e:\5-digit Commercial.csv'  DECLARE @bulkinsert NVARCHAR(2000)  SET @bulkinsert =         N'BULK INSERT ZIPCodes FROM ''' +         @filepath +         N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'  EXEC sp_executesql @bulkinsert 

Answer 2

There is an alternative to dynamic SQL if you have access to the SQLCmd exe.

The SqlCmd utility allows you to pass string replacement variables using the -v argument.

You can use a template variable named "filepath" which will be replaced when you execute the script via the cmdline.

The SQL script would look like:

BULK INSERT ZIPCodes  FROM  '$(filepath)'  WITH  (       FIRSTROW = 2 ,     FIELDTERMINATOR = ',',      ROWTERMINATOR = '\n'  ) end 

You would then execute the script from a commandline using something like the following:

sqlcmd -b -S SERVER\INSTANCEHERE -E -i "PATH\FILENAMEHERE.Sql" -v FilePath = "e:\5-digit Commercial.csv" -s "|" 

The important part of the example is the -v argument:

-v FilePath = "e:\5-digit Commercial.csv"