SQL Server bulk insert stored procedure-CodePudding

I am using SSMS and trying to create a stored procedure (because it needs to survive batches) ,so i can bulk insert from multiple csv files (one at a time) into specific tables.

So far I have:

CREATE PROCEDURE AddDataToTable @TableName VARCHAR(25), @DataFolderPath VARCHAR(250),
@DataFile VARCHAR(50), @FieldDeterminator VARCHAR(10)
AS
BEGIN
DECLARE @SQL_BULK VARCHAR(MAX)
SET @SQL_BULK =
'BULK INSERT ' @TableName '
FROM ''' @DataFolderPath @DataFile '''
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ''' @FieldDeterminator ''',
ROWTERMINATOR = ''0x0A'',
TABLOCK
)'
PRINT @SQL_BULK
EXEC @SQL_BULK
END
GO

And using with

EXEC AddDataToTable 'dbo.People',@DataFolderPath,'\ImdbName.csv',';'

And I get the error:

Msg 911, Level 16, State 4, Procedure AddDataToTable, Line 18 (Batch Start Line 161) Database 'BULK INSERT dbo' does not exist. Make sure that the name is entered correctly.

The thing is that I also added a print statement in the procedure and the print result looks like it should with every quote which is:

BULK INSERT dbo.People
FROM 'C:\Users\PC\Desktop\Data\ImdbName.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '0x0A',
TABLOCK
)

CodePudding user response：

You are using a wrong syntax. Execute the dynamically generated statement like this:

EXEC (@SQL_BULK)

DECLARE @err int
EXEC @err = sp_executesql @SQL_BULK
IF @err <> 0 PRINT 'Error found.'

As an additional note, always use QUOTENAME() to prevent possible SQL injection issues, when you generate an SQL Server identifier from an input string:

SET @SQL_BULK = 'BULK INSERT '   QUOTENAME(@TableName)   ' FROM ... '