Temporary Table - Maximum allowed number of 1000 row values

Question

When trying to insert 6000 rows into a temp table I get the following message

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Source is not located in SQL Server.

CREATE TABLE #TMP_ISIN (
   [Isin] nVARCHAR(250))

INSERT INTO #TMP_ISIN ([Isin])
VALUES
ABOUT 6000 ROWS

How shall I do to avoid this limit?

Answer 1

The limit of 1000 is on the number of rows in the values clause of the insert rather than a limitation of the temporary table itself:

The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000. Error 10738 is returned if the number of rows exceeds 1000 in that case.

To insert more than 1000 rows, use one of the following methods:

• Create multiple INSERT statements;
• Use a derived table;
• Bulk import the data by using the bcp utility or the BULK INSERT statement.

Hence you can do it in chunks, with smaller insert statements.

insert into sometable (somecolumns) values <about 1000 rows>;
insert into sometable (somecolumns) values <about 1000 rows>;
:
insert into sometable (somecolumns) values <about 1000 rows>;

If you need all 6000 to be atomic, you can put a transaction around the whole thing.