Inserting more than 1000 rows using the table value constructor as a derived table

Question

I have the following T-SQL query which inserts a list of values into a temporary table. My issue is that the INSERT function is limited to 1,000 rows and I have a list of 4,000 rows.

Here is an extract of the query:

USE MyDatabase

create table #t1 
( 

ResaID numeric (20) NOT NULL,
CtyRes varchar (20) NOT NULL

); 

INSERT INTO #t1 VALUES

('304475','GB'),
('304482','GB'),
('304857','GB'),
('314643','GB'),
('321711','GB'),
('321714','GB'),
...

...and the list goes on till Row 4,000

As per Microsoft documentation, this limitation can be bypassed using a table value constructor as a derived table.

Example from Microsoft: Inserting more than 1,000 rows

CREATE TABLE dbo.Test ([Value] int);  

INSERT INTO dbo.Test ([Value])  
  SELECT drvd.[NewVal]
  FROM   (VALUES (0), (1), (2), (3), ..., (5000)) drvd([NewVal]);

How do I modify my existing SQL query to adapt it to this example?

Answer 1

You could extend MS example to handle multiple columns by using:

INSERT INTO #t1(ResaID, CtyRes) 
SELECT ResaId, CtyRes
FROM (VALUES 
('304475','GB'),
('304482','GB'),
('304857','GB'),
('314643','GB')) AS sub( ResaId, CtyRes);

db<>fiddle demo