3

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?

user3115933
  • 4,303
  • 15
  • 54
  • 94
  • 1
    What about the documentation don't you understand; it shows you how to change the logic. What's wrong with the example displayed there? – Thom A Aug 15 '19 at 09:30
  • [How do I insert multiple rows WITHOUT repeating the “INSERT INTO dbo.Blah” part of the statement?](https://stackoverflow.com/a/42703601/5070879) - example with 2 columns – Lukasz Szozda Aug 15 '19 at 09:31
  • 1
    @Larnu OP wants to extend this logic for multiple columns. MS example shows how to do it for single one – Lukasz Szozda Aug 15 '19 at 09:32
  • @Larnu I am not saying it's wrong. I am just having some difficulties given in my case, I have 2 values for each row. – user3115933 Aug 15 '19 at 09:32
  • @user3115933 Please check the link I provided – Lukasz Szozda Aug 15 '19 at 09:33
  • Yes, but a `SELECT` can return multiple columns `SELECT Col1, Col2 FROM (VALUES('a','b')) V(Col1,Col2);` – Thom A Aug 15 '19 at 09:33
  • You should mark this as a dup, @LukaszSzozda, as that gives exactly what the OP needs. – Thom A Aug 15 '19 at 09:35
  • 1
    @LukaszSzozda Thanks for the link. exactly what I needed. If you post as an answer, I will vote accordingly. – user3115933 Aug 15 '19 at 09:35

1 Answers1

3

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275