SQL Server – Breaking a Big Insert Query into Smaller Ones

sql servert-sql

I have the below INSERT query. It tries to insert over 50,000 rows and it fails because of logging. Is there a good way of splitting a query like this into batches of about 1000 or so with COMMIT statements so that it is not just one big statement?

INSERT INTO xxx (....) SELECT .... FROM xx

Best Answer

DECLARE @rc INT = 1;
WHILE @rc > 0
BEGIN
  BEGIN TRANSACTION;

  INSERT dbo.target(cols) 
    SELECT TOP (5000) cols 
    FROM dbo.source AS s
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.target AS t
      WHERE t.key = s.key
    )
    ORDER BY clustering_key;

  SET @rc = @@ROWCOUNT;
  COMMIT TRANSACTION;
END

Also see:

Break large delete operations into chunks

Related Solutions

Sql-server – Splitting SQL query with many joins into smaller ones helps

1 Reduction of 'search space', coupled with better statistics for the intermediate/late joins.

I've had to deal with 90-table joins (mickey mouse design) where the Query Processor refused to even create a plan. Breaking such a join into 10 subjoins of 9 tables each, dramatically brought down the complexity of each join, which grows exponentially with each additional table. Plus the Query Optimiser now treats them as 10 plans, spending (potentially) more time overall (Paul White may even have metrics!).

The intermediate result tables will now have fresh statistics of their own, thus joining much better compared to the statistics of a deep tree that become skewed early on and end up as Science Fiction soon afterwards.

Plus you can force the most selective joins first, cutting down the data volumes moving up the tree. If you can estimate the selectivity of your predicates much better than the Optimiser, why not force the join order. Might be worth searching for "Bushy Plans".

2 It should be considered in my view, if efficiency and performance are important

3 Not necessarily, but it could be if the most selective joins are executed early on

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Best Answer

Related Solutions

Sql-server – Splitting SQL query with many joins into smaller ones helps

Sql-server – Combine – Select Into, with Insert Into Select

Related Question