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
>