Hello,

I'm inserting into a table using a while loop using the T-SQL script of similar structure below. ( number columns and transformation in the select reduced for brevity) :

WHILE EXISTS( select 1 FROM #ID)

BEGIN

SELECT @ID = ID FROM #ID

INSERT INTO Table1

SELEC col1,col2,col3

FROM table2

WHERE col4 = @ID

DELETE FROM #ID WHERE ID = @ID

END

The script runs under 0.01seconds when I run for one ID value but the total time to process the records is doesn't correlate with the time taken to process 1 ID value. The total records inserted are ~3.6 million. The number of records per ID value vary between 2 - 1300.

I've tried to insert in batches of 10000 records but total time to finish INSERTing all records doesn't correlate with the time taken to insert a single batch. The destination table does not have any indexes. The source table - table2 has a clustered index on col4. The temp table #ID has 10000 records and has an index too.   I have tried wrapping the INSERT in an explicit BEGIN TRAN COMMIT TRAN but that doesn't help

Can you please throw some light on the issue? and how to fasten this type of setup.

How long does the below take. It is pretty fast on my server

DECLARE @x INT
SET @x = 1
WHILE @x < 3000000  -- Set appropriately
BEGIN

    INSERT INTO #t SELECT * FROM tbl WHERE c4 BETWEEN @x AND @x + 10000

    SET @x = @x + 10000
END

Hello - I am wondering why are you using WHILE Loop for this. You are essentially using row-by-row processing which kills the performance by all means

In my opinion this can be easily done by using a standard T-SQL Set based query, consider this:

-- Insert using Join
INSERT INTO Table1
SELECT col1,col2,col3 
FROM Table2 T1 INNER JOIN #ID T2
ON T1.Col4 = T2.ID

-- Truncate/ Drop Table #ID
DROP TABLE #ID