Sql Server Bulk Insert into temporary table syntax error

Question

I've been reading up on tutorials and other stackoverflow answers but I just can't seem to get this to work. I'm running the following script and I keep getting.

USE my_db
GO

DECLARE @TempTable TABLE (
insz nvarchar(max),
firstname nvarchar(max),
middlename nvarchar(max),
lastname nvarchar(max),
birthdate date,
street nvarchar(max),
streetnumber nvarchar(max),
mailbox nvarchar(max),
city nvarchar(max),
zipcode nvarchar(max)
)
GO

BULK INSERT @TempTable
FROM 'C:\Workspaces\magdasync\src\main\examples\my_file.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
GO

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '@TempTable'.

Remove GO before BULK statement. Table variable is visible only in its scope. Also it turns out that you can not insert into table variable: stackoverflow.com/questions/14113820/… — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Sep 5, 2016 at 9:03
Well I ended up with a different error now Msg 102, Level 15, State 1, Line 15 Incorrect syntax near '@TempTable'. Msg 319, Level 15, State 1, Line 17 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. — kenny
– kenny, Commented Sep 5, 2016 at 9:04

Mohit Dagar · Accepted Answer · 2016-09-05 09:57:09Z

2

It is not possible to bulk insert in table variable. so you can use temp table.

USE magdasync
    GO

CREATE Table #TempTable(
insz nvarchar(max),
firstname nvarchar(max),
middlename nvarchar(max),
lastname nvarchar(max),
birthdate date,
street nvarchar(max),
streetnumber nvarchar(max),
mailbox nvarchar(max),
city nvarchar(max),
zipcode nvarchar(max)
)
GO

BULK INSERT #TempTable
FROM 'C:\Workspaces\magdasync\src\main\examples\magdasync_input_example.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
GO

answered Sep 5, 2016 at 9:57

Mohit Dagar

5701 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kenny Over a year ago

There are some old answers on stackoverflow that are deprecated then cause @ is certainly not working anymore

Collectives™ on Stack Overflow

Sql Server Bulk Insert into temporary table syntax error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related