Categories
bulkinsert sql sql-server-2008 stored-procedures

Bulk insert using stored procedure

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

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