May 14, 2004 at 9:14 am
Hello! This is my first post here. Man, am I glad I found this place. I need some advice!!!
I am running SQL Server 2000. I have a large insert to do on a table. (23 million records) When I run my script to do the insert, the transaction log grows until the log is full. Is there a way to turn off logging temporarily? My Recovery Model is set to "Simple".
Thanks in advance.
Joel
May 14, 2004 at 9:32 am
How are you doing the insert? You cannot turn off logging, but you can BCP in with different logging or commit after xx rows to minimize the log growth.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 14, 2004 at 10:21 am
Thanks for the quick reply.
I have a sql 2k table with 23 million rows. Call it BigTable. I have an empty table that has the same structure as BigTable, with the exception of the first column being: id_num int IDENTITY(1,1). Call this one NewTable.
Here's my script:
insert into NewTable(Col1, Col2, Col3, Col4, Col5)
select Col1, Col2, Col3, Col4, Col5 from BigTable
That's it!!! I just need to put the 23 million records into NewTable while adding a newly numbered IDENTITY column.
How can I accomplish this with minimal logging? (BTW...I've never used BCP before.)
May 14, 2004 at 12:43 pm
there is nothing hard using with bcp as steve suggested, but if you are still not comfortable, use the dts. it has options there to commit every xxx rows.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply