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

copying large SQL SERVER tables between DB's

Hi
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
aviIf you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
--
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>

copying large SQL SERVER tables between DB's

Hi
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
aviIf you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>

copying large SQL SERVER tables between DB's

Hi
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
avi
If you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>
|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>