SELECT INTO with COMMIT every x

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)

SELECT INTO with COMMIT every x

Author

Topic

TimChenAllen
Starting Member

45 Posts

Posted - 2003-11-26 : 05:11:48

I am creating a table with a SELECT INTO statement. It is a large table and I would like to do a commit for every 1000 inserts. The "dream query" would look like this:


SELECT base.id, base.col1, joined.col2
INTO   newtable COMMIT_EVERY 1000
FROM   base
LEFT OUTER JOIN joined on base.id = joined.id

Note the "dream" clause "COMMIT_EVERY 1000". How can I do this? Thanks in advance.

--
TimothyAllen
[url]http://www.timallen.org[/url]

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-26 : 08:00:50

The main way I see to attack this is on the lines of the following.

while @i > 0
begin tran
set rowcount 1000
SELECT base.id, base.col1, joined.col2
INTO newtable
FROM base
LEFT OUTER JOIN joined on base.id = joined.id
where base.id > @maxid
order by base.id
select @i = @@rowcount
select @maxid = max(newtable.id) from newtable
end tran
wend

naturally you'll have to declare @i and @maxid...a "where not exists" on the newtable may be possible...but (lack of) time prevents me from supplying that solution.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-11-26 : 08:03:29

Might make it easier if you

SET IMPLICIT_TRANSACTIONS ON

but keep an eye out for your @@TRANCOUNT

________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources