Insert values 1000 row limit

Asked Oct 29 '19 at 17:21

Active Oct 29 '19 at 17:33

Viewed 40 times

I need to insert 26K rows of values into a temp table but run into 1,000 limit error. Is there any work around to this?

I will need to this type of insert many times and need to use temp tables for all my work.

IF OBJECT_ID('tempdb..#Budget') IS NOT NULL 
BEGIN
    DROP TABLE #Variables 
END

CREATE TABLE #Budget 
(
     [SBU] VARCHAR(3),
     [B_Type] VARCHAR(50), 
     [D_Type] VARCHAR(50),
     [C_Direct] VARCHAR(50),
     [Dest] VARCHAR(50),
     [D_Month] VARCHAR(50),
     [D_Year] INT,
     [Pax] INT, 
     [Rev] INT
)

INSERT INTO #Budget ([SBU], [B_Type], [D_Type], [C_Direct], [Dest], [D_Month], [D_Year], [Pax], [Rev])
VALUES (.....)

edited Oct 29 '19 at 17:33

marc_s

704,970
168
1,303
1,425

asked Oct 29 '19 at 17:21

Tim Mullady

Insert 999 at a time. – Robert Harvey Oct 29 '19 at 17:21
Or, use a bulk sql copy. – Robert Harvey Oct 29 '19 at 17:22
Or, if your source data can be retrieved using SELECT, try a SELECT INTO. – Robert Harvey Oct 29 '19 at 17:23
Does this answer your question? [Temporary table record limit in Sql server](https://stackoverflow.com/questions/10084857/temporary-table-record-limit-in-sql-server) – Kade M. Oct 29 '19 at 17:23
@KadeM. https://stackoverflow.com/a/30469300 – Robert Harvey Oct 29 '19 at 17:24
See here: https://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values/8640583#8640583 – Kade M. Oct 29 '19 at 17:24
It's only when using `INSERT INTO...VALUES` that it's limited. `INSERT INTO...SELECT ...FROM(VALUES...)` isn't. [DB<>Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=48e7d7cb0104cd89650c34654a21fc73) – Larnu Oct 29 '19 at 18:06
@DB<>Fiddle how can I do that into a temp table? – Tim Mullady Oct 29 '19 at 18:59
Unfortunately, I'm creating the source data. it doesn't exist in any of my data sets. inserting 999 rows is a option but this will need to be completed all the ongoing – Tim Mullady Oct 29 '19 at 19:01
using individual insert statement for each line works. – Tim Mullady Oct 29 '19 at 20:22

Insert values 1000 row limit

0 Answers0