Search code examples
sqlsql-serversql-insertinsert-into

Insert into Table from multiple sources simultaneously - SQL Server

I need to insert data to a table from 4 different sources. Approx 2M rows per source. I'm doing it with 4 different procedures and I don't want to change it.

Is there any problem in inserting it simultaneously?

Insert into TableA
Select * 
From  TableB

Insert into TableA
Select * 
From  TableC

Insert into TableA
Select * 
From  TableD

Insert into TableA
Select * 
From  TableE

Appreciate your help!

Solution

  • There are couple of option you can try.

    1. Batched Operation: Select * from table1 is not a good approach as it can create a implicit lock on the table. Bring the data in batches. Finding the right batch size has to be worked out with a few trials but I believe a batch size of 30-50K should be ok.

    2. Option is to use SSIS packages, most of the processing complexity is take care of you so you should be able get the ground running quickly and if its a frequently reused then create SQL job and let the package be called.

    A few reference links to get your head spinning

    a) SSIS b) Batch

    Use me -> Google