CODEWITHSUNDEEP
sqlsql-servert-sql
chris
chris

Reputation: 605

Whats wrong with this SQL statement for table variable bulk insert

I'm trying to insert a CSV into a Temporary table and this SQL statement doesn't seem to work.

DECLARE @TempTable TABLE (FName nvarchar(max),SName nvarchar(max),
                          Email nvarchar(max));
BULK INSERT @TempTable 
FROM 'C:\52BB30AD694A62A03E.csv' 
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

Getting the following error....

Incorrect syntax near '@TempTable'.

Upvotes: 12

Views: 26948

Answers (4)

RBarryYoung
RBarryYoung

Reputation: 56785

You cannot BULK INSERT into a table variable. So this line:

BULK INSERT @TempTable

Is what is causing the error.

FYI, the simplest fix for this is probably just to use a #Temp table instead of a Table Variable. So your SQL code would change to this:

CREATE TABLE #TempTable (FName nvarchar(max),SName nvarchar(max),
                          Email nvarchar(max));
BULK INSERT #TempTable 
FROM 'C:\52BB30AD694A62A03E.csv' 
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

Upvotes: 21

Sutirth
Sutirth

Reputation: 952

you can not use bulk insert for table variable. for that you have create temp table like below.

CREATE TABLE #TEMPtbl 
(
    [FNAME] [nvarchar](MAX) ,
    [SNAME] [nvarchar](MAX) ,
    [EMAIL] [nvarchar](MAX) 
)
GO 
BULK INSERT #TEMPtbl FROM 'C:\FileName.csv' 
WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

you can try This one. it may be help you.

Upvotes: 1

rs.
rs.

Reputation: 27467

You cannot use table variable when using BULK INSERT

You can try this

DECLARE @TempTable TABLE (FName nvarchar(max),SName nvarchar(max),
                          Email nvarchar(max));
INSERT INTO @TempTable
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Users\Administrator\Dropbox\Personal\testing.vineup.com\admin\imported;HDR=Yes;FORMAT=Delimited(,)', 'SELECT * FROM [52BB30AD694A62A03E.csv]')

Upvotes: 6

Woot4Moo
Woot4Moo

Reputation: 24336

I think you want to do something like this: 

DECLARE @sql NVARCHAR(8000)
SET @sql = 
'
BULK INSERT #TempTable ...' ;

What you are doing is trying to force a variable into a non-dynamic sql statement. So the compiler/interpreter (not sure which is the correct term for SQL) is bombing out since it cannot properly parse it.

Upvotes: -1

Related Questions