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:52円BB30AD694A62A03E.csv' 
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

Getting the following error....

Incorrect syntax near '@TempTable'.

• rs.: You cannot use table variable, you need real table when using BULK INSERT
• rs.: @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.: @Woot4Moo you said that my comment is not true. so i wanted to know which part of my comment is not true :)
• rs.: @Woot4Moo its a temp table name not variable
• Andriy M: @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?.

Answer 1

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:52円BB30AD694A62A03E.csv' 
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

Answer 2

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

Answer 3

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.

Answer 4

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.