copying tables in SQL SERVER

I have a 100m row table that I need to come from one database to another database in SQL SERVER.

The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.

Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)

and on a 100m row table it is taking around 52 hours. Not acceptable.Is it on the same server?

bcp out in native format and bcp in is probably the fastest

But the SELECT * INTO is a minimally logged operation...and if it's on the same server...

I'm not so sure bcp would beat it since it's 1 operation as compared to 2.|||Yes it is on the same server.|||For 100 million rows, I think I'd partition it up anyway...

What's the DDL of the table...is it a heap or does it have a pk?

If it's got something unique, I'd split it up in to 10 tables and thread out the SELECT Collist INTO Table1
SELECT Collist INTO Table2
SELECT Collist INTO Table3
SELECT Collist INTO Table4
ect

And run all 10 at the same time from 10 separate osql bat files...

thats 1 select per file....

How long does a backup take?|||Hello Everyone,

If you would like to copy records within the same database between the different databases the you can use this query,

insert into <table Name> select * from <Destination Table>|||insert into <table Name> select * from <Destination Table>The only problem is that this syntax is fully logged, while the SELECT INTO syntax is only minimally logged. Since the SELECT INTO appears to be too slow and I'd expect this to be even slower, I don't think it would be a good solution.

-PatP|||Damn...I wonder if that belongs here...

http://thedailywtf.com/archive/2004/09/01/1511.aspx