Whats wrong with this SQL statement for table variable bulk insert

Question

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'.

You cannot use table variable, you need real table when using `BULK INSERT` — rs., Jan 01 '13 at 20:19
@Woot4Moo which part? Can you explain? And can you post answer how to do insert into table variable, not use temp table like you did in your answer? — rs., Jan 01 '13 at 20:24
@Woot4Moo you said that my comment is not true. so i wanted to know which part of my comment is not true :) — rs., Jan 01 '13 at 20:38
@rs. ah yes, afaik (correct me if im wrong please) the `#name` is referencing a variable, if it isn't a variable what is it? — Woot4Moo, Jan 01 '13 at 20:39
@rs. if you could expound how that isn't a variable declaration I am most curious. — Woot4Moo, Jan 01 '13 at 20:44
All temp tables are created with `#` as prefix. You create temp table using `create #temp (id int);` which creates real table in database and not variable. — rs., Jan 01 '13 at 20:49
@Woot4Moo: Just in case you aren't sure yet how `#tables` are different from `@tables`, have a look at this question: [What's the difference between a temp table and table variable in SQL Server?](http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server). — Andriy M, Jan 02 '13 at 13:10

RBarryYoung · Accepted Answer · 2013-01-02T19:46:28.093

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')

score 5 · Answer 2 · answered Jan 01 '13 at 20:27

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]')

score 0 · Answer 3 · answered Jan 02 '13 at 04:55

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.

score -1 · Answer 4 · answered Jan 01 '13 at 20:17

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.

Whats wrong with this SQL statement for table variable bulk insert

4 Answers4

Linked