0

Creating Table Variable to insert into Temporary Table. But I need to repeat my insert into @TableVariable VALUES every 1000 records. How to bypass insert into 1000 records limit?

Current script is:

declare @@globalVariable Table (ID int, LName varchar (30), FName(30))
INSERT INTO @@globalVariable VALUES 
(1,'Johnson','John'),
...... row # 1001
INSERT INTO @@globalVariable VALUES 
(1001,'Polinski','Samuel'),
...... row # 2001
INSERT INTO @@globalVariable VALUES 
...... row # 3001
Select * Into #TempTable from INSERT INTO

I need to avoid repeating [INSERT INTO @@globalVariable VALUES] every thousand rows, as my SSMS throws an error when not repeating it every 1000 rows.

Benjamin W.
  • 46,058
  • 19
  • 106
  • 116
Data Engineer
  • 795
  • 16
  • 41
  • 1
    Can you show us your existing script ? and what do you mean by `bypass insert into 1000 records limit` ? – Krishnraj Rana Apr 22 '16 at 12:11
  • Have you set the row limit in SSMS to 1000? – James Z Apr 22 '16 at 13:31
  • 1
    Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example. – Tab Alleman Apr 22 '16 at 13:32
  • Possible duplicate of [Inserting more than 1000 rows from Excel into SQLServer](http://stackoverflow.com/questions/24328730/inserting-more-than-1000-rows-from-excel-into-sqlserver) – Mark Sinkinson Apr 22 '16 at 13:59
  • Can you try something like the following? `INSERT INTO @@globalVariable SELECT * FROM (SELECT 1 AS Id,'Johnson','John' UNION ALL SELECT 2 AS 2 As Id, 'LastName 2', 'FirstName 2' ...) T`. Also, what is the error provided by SSMS? – Alexei - check Codidact Apr 24 '16 at 05:12
  • I would say, that suggested solution is not good when inserting thousands of records. So I think the only quick solution would be to build a formula in Excel to repeat INSERT INTO @@globalVariable VALUES (1001,'Polinski','Samuel'), INSERT INTO @@globalVariable VALUES (1002,'Strader','Steward') for every set of values. So, this will eliminating 1000 rows limit... – Data Engineer Apr 25 '16 at 03:32

1 Answers1

0

Union All is not a suggested solution when inserting thousands of records. So I think the only quick solution would be to build a formula in Excel to repeat 

declare @@globalVariable Table (ID int, LName varchar (30), FName(30))
INSERT INTO @@globalVariable VALUES (1,'Johnson','John'),
INSERT INTO @@globalVariable VALUES (2,'Strader','Steward'),
INSERT INTO @@globalVariable VALUES (3,'Joany','Robert'),
...... row # 1001
INSERT INTO @@globalVariable VALUES (1001,'Polinski','Samuel'),

Select * Into #TempTable from INSERT INTO
Data Engineer
  • 795
  • 16
  • 41