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.
| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2002-09-13 : 19:45:58
|
| SQL 2000:We are creating some horizontally partitioned tables and we need to move data from our 7+million record table into the smaller tables.If we use INSERT INTO...SELECT and DELETE FROM... it will put MUCH stuff in our transaction log (right?).How do we make it not do that? I'm thinking that we need to change the recovery mode to "Simple", run the script, and then change it back to full.Any help would be appreciated!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-13 : 20:06:51
|
| AFAIK INSERT INTO...SELECT is always logged regardless of recovery model (SELECT...INTO however is minimally logged) You can however manage the operation by using SET ROWCOUNT to limit the number of rows inserted each time, in addition to BACKUP LOG...WITH NO_LOG to clear it after each batch. I've had to do this a few times and it works pretty well, just make sure you make a full backup before you start in case anything goes wrong. |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-09-14 : 02:02:42
|
| Michael,Why not BCP OUT then BCP IN?DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
INSERT INTO..SELECT Non-Logged