batching template - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

batching template

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-19 : 03:35:08

I have a query similar to this:

SET ROWCOUNT 10000
DECLARE @intRowCount int
SELECT @intRowCount=1
WHILE @intRowCount > 0
BEGIN
INSERT INTO DestinationTable
SELECT *
FROM SourceTable
WHERE NOT EXISTS (SELECT * FROM DestinationTable WHERE DestIDColumn = SourceIDColumn)
SELECT @intRowCount = @@ROWCOUNT
END

this took 4 minutes for 210 000 rows

I tried to improve the performance by adding transactions:
SET ROWCOUNT 10000
DECLARE @intRowCount int
SELECT @intRowCount=1
WHILE @intRowCount > 0
BEGIN
BEGIN TRANSACTION
INSERT INTO DestinationTable
SELECT *
FROM SourceTable
WHERE NOT EXISTS (SELECT * FROM DestinationTable WHERE DestIDColumn = SourceIDColumn)
SELECT @intRowCount = @@ROWCOUNT
COMMIT
END

but this was still running 14 hours later!

Is there a way to accomplish the same result (batch inserting) in a statement without the performance cost of
"WHERE NOT EXISTS (SELECT * FROM DestinationTable WHERE DestIDColumn = SourceIDColumn)"
?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources