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.

Answer 2

It's only an issue with the VALUES clause. If you're inserting more than 1000 rows by this method (questionable approach but hey) then use a set of SELECT statements with UNION ALL:

INSERT #a (a,b)
SELECT 'a', 'b' UNION ALL
SELECT 'c', 'd' UNION ALL
SELECT ...etc

This also has the advantage that you can check the resultset from the SELECT statements prior to doing the INSERTs - you won't get that luxury with the VALUES construct.

Answer 3

It’s very easy to get around this limitation by using what Microsoft refers to as a Derived Table:

DROP TABLE IF EXISTS #test;
CREATE TABLE #test(
    id integer identity(1,1) primary key,
    name varchar(48),
    etc varchar(255)
);

INSERT INTO #test(name,etc)
SELECT * FROM (     --  add this line
VALUES
    ('apple','Red round thing with a worm in it.'),
    ('banana','Long yellow thing to feed monkeys.'),
    ('cherry','Small black thing for cocktails.')
    --  thousands more rows
) AS whatever(a,b)  --  add this line
;

The code is similar to the INSERT … VALUES …, but copies the values from a virtual derived table.

As you see, the alias for the VALUES is whatever, and both the alias and the columns are arbitrary; they do not affect the data copy in any way.

The solution comes indirectly from https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#c-specifying-multiple-values-as-a-derived-table-in-a-from-clause .

Seriously I cannot see why Microsoft impose this idiotic limit of 1000 rows for an INSERT statement if it’s so easy to work around it.

I guess it’s a Microsoft thing … 1000 rows ought to be enough for anyone.