Improve INSERT INTO - FROM SELECT, SQL Query

Question

Currently I got this type of query generated by programmation (c#)

INSERT INTO TableName (Field1, Field2, Field3)
SELECT Field1, Field2, Field3 FROM TableName2

The problem is that the SELECT can have a result of many records (like a million), so it's take many times and the result is an connection timeout.

Also, if I separate all the insert into in single insert (for this example, one million insert queries), it's take very long time to execute ... but it work ...

Is there a way I can improve this type of query ?

I use MSSQl 2005

Thx

Answer 1

I've found out that, if you have a lot of INSERT statements that are executed in sequence, you can improve performance by adding a 'GO' statement after each xxxx number of insert statements:

...
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
GO
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
...

Another possibility maybe, is to make sure that your INSERT INTO .. SELECT FROM query doesn't insert everything in one time, instead use some kind of paging technique:

INSERT INTO Table ...
SELECT ...
FROM OtherTable WHERE Id > x and Id < y