CODEWITHSUNDEEP
sqlsql-servercsv
Jason Samuels
Jason Samuels

Reputation: 971

Variables in MS SQL Bulk Insert path string

I need to import 30 .csv files into an MS SQL SERVER database. I can use BULK INSERT, but because of the number of files I want to do it with loop. Each file is named as DP(1).csv, DP(2).csv, DP(3).csv, ..., DP(30).csv.

I did write a WHILE loop and with a counter that can also be used to identify a file name, but I have problems with the syntax when including the counter variable in the path name. Here is my code:

DECLARE @COUNT INT

SET @COUNT = 1
USE Db_Pc 

WHILE @COUNT <= 30
    BEGIN
        BULK INSERT acks FROM 'C:\Users\JASON SAMUELS\Documents\M-DD\DP('+@COUNT+').csv' 
      WITH(FIRSTROW = 2, 
           ROWTERMINATOR = '\n',  
           FIELDTERMINATOR = ',')

       SET @COUNT = @COUNT + 1
    END

The error seems to be at the first + just before the variable @COUNT in the path string. This is the error message i get:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'.
Msg 319, Level 15, State 1, Line 8
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.

How should i insert the variable in the string?

Upvotes: 1

Views: 1132

Answers (1)

Brad Allred
Brad Allred

Reputation: 7534

it's because you are concatenating an int to a string.

you need to use CAST( @COUNT as varchar(X) ) (or is it CONVERT?).

try:

BULK INSERT acks FROM 'C:\Users\JASON SAMUELS\Documents\M-DD\DP(' + CAST(@COUNT as varchar(5) ) + ').csv' WITH(FIRSTROW = 2, ROWTERMINATOR = '\n', FIELDTERMINATOR = ',')

Sorry I'm not around a place I can test the exact syntax.

Upvotes: 1

Related Questions