CODEWITHSUNDEEP
sql-serverinsertbulkinsertdatabase-performance
praveen
praveen

Reputation: 111

How to insert data from one table to another in a loop considering performance?

I need to insert data from one table to another in a loop with 10k rows in each loop run.

I want to try this because my insert into ***() select () from ### takes so much time as source table is having some millions of records.

Will there be any improvement in performance by using this approach?

Upvotes: 1

Views: 3568

Answers (1)

Sonam
Sonam

Reputation: 3466

You can try inserting in batches and see if there is any performance improvement. Here is the sample code:

DECLARE @Count INT
DECLARE @Start INT

SET @Start = 1
SELECT @Count = COUNT(*) FROM TableName1

WHILE @Start<=@Count
BEGIN
    WITH cte
    AS
    (
        SELECT Col1, Col2, Col3,ROW_NUMBER() OVER (ORDER BY Col1) AS 'RowNum' FROM TableName1
    )

    INSERT INTO TableName2 SELECT Col1, Col2, Col3 FROM cte WHERE RowNum >= @Start AND RowNum < @Start+10000

    SET @Start += 10000

    WAITFOR DELAY '00:00:10'
END

Here 10000 is the batch size and you can change this value as per your convenience.

Upvotes: 4

Related Questions