T-sql – How to insert numerous data into temporary table

bulkinsertt-sql

I'm building a program which generates a T-SQL query in following form:

DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES value1, value2... value1000
GO
INSERT INTO @In VALUES value1001, value1002...

but the second INSERT statement throws error:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@In".

What am I doing wrong?

Best Answer

You can use VALUES (...), (...):

INSERT INTO table(colA, colN, ...) VALUES
    (col1A, col1B, ...)
    , ...
    , (colnA, colnB, ...)

With @In:

DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES 
    ('value1')
    , ('value2')
    , ...
    , ('value1000')

It will insert X rows all at once. GO is not needed. Variables declared before GO don't exist anymore after GO.

Related Question