copy data in a temp table before archiving

[suznCB]

Dears, kindly help in the following case:

I want to archive a very large table in our production database, by moving it's data to an exact table in another database on another server, then delete data from the original table.

but I want to do this archiving periodically, this means, I need to set a range of data to be moved , I'm planning to use bcpout, then bcp in in the target table,

as we cannot set a range by this tool , I'm thinking to insert part of data in a temp table

(select * into tempdb..tbl1 from tbl1 where date between @date1 and @date2),

then use bcpout on this temp table.

But the case is the data which will copied in the temp table is about 300 MB, while the unused space in the temp database is just 120 MB.

this temp database in the production server, and it is used besides the prod database in many application,

is there any hint to over this problem, or there is another way to archive my data?

please help.

[former_member188958 · Accepted solution]

Perhaps every period you could create a view that selected just the data you wanted to archive?
BCP is able to bulk copy out of a view.

Another option might be to create a proxy table linked to the archive table on the other server and insert the rows into the proxy table.

-bret

[suznCB · Accepted solution]

Thank you very much