Need to cleardown a table due to disk space problems

I need to delete about 3 million rows from a table that is part of a merge
publication. I think that I will have to copy out the rows that I want to
keep into a temp table and truncate the table and then copy the rows back in.
My question is how best to go about this? I think that the best way is to go
into the publication properties and uncheck the table on the articles tab.
Then to carry out the same process of copying out the data to be kept,
truncate the table, then move the rows to be kept back in. Then add the
article back into the publication. In order for the article to replicated
after adding it back in would I have to do a snapshot or would it resume by
itself?
Russell,
this sounds OK. However if the publication already has a subscription, you
won't be able to remove the individual article and you'll have to drop the
entire subscription before proceeding. You could drop the subscription,
remove the rows on publisher and subscriber then do a nosync initialization.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

need some help, making many rows out of one, millions of times

so here's the deal:

i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.

Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.

Here's what the data looks like

location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
efffrom date
effto date -- described above

does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?

i'm stumped and in dire need of some inspiration. thanks in advance.See this thread for help on using a table of sequential values to "fill in" dates in a date range:

http://dbforums.com/showthread.php?threadid=914261

Once you create your sequential value table, use it in a query like this:

Select YourFields,
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)

I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.

Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.

blindman|||awesome...the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.

would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.

thanks!!!!|||I've never liked DTS, and use it mostly for simple data transfers.

A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

blindman