Is a process to BCP the "keepers" out and then back in faster? Most likely,
yes. Some information is logged (page/extent allocations on the new table)
on a fast BCP but it's really minimal. Truncate table logs just page/extent
deallocations, I think. Check the documentation to see what's necessary to
get a "fast" bcp in.
If you have enough disk space available to SQL Server, an alternative might
be to:
1. Create a script of your original table.
2. Select * into keepers_table from original_table where <criteria for
records you want to keep>
3. Drop original_table (or rename it)
4. sp_rename keepers_table, original_table
This is potentially faster than bcp out/in but you might have trouble with
the resulting table definition. I'm not sure if the keepers_table created
by the "into" clause would preserve nullability, for instance. Defaults,
primary keys and constraint checks would be lost. You can add some
characteristics back with table alters (that's where the script of the
original table comes in). You'd want to test this with a small quantity of
data, first.
You could also be a little extra tricky and, I think, do this process inside
a transaction. I think you could include adding your constraints and
whatnot in the transaction, too.
Finally, what's your hurry? Have you created a script, process or stored
procedure to manage deleting 130-odd .5MB chunks of the original table? If
so, that seems like a manageable, if slow, process.
"rc" <rc@spam.com> wrote in message
news:3m********************************@4ax.com...
Hi
I have a SQL2000 server with 128m rows of data. I want to delete about
65m of that. So far I have bcp'ed the relevent data out and put them
into another SQL database.
We have a small amount of space for our transaction log so I cannot
delete all 65m rows in one go. So far I have been doing them is 0.5m
chuncks, but it is extremly slow.
Would a faster way be to bcp the data I wan to keep and truncate the
table and bulk import them in again ?
What hapnes to log size in when builk import is happening and is there
another way of doing this ?
Thanks for any help