Posted by Erland Sommarskog on 11/24/05 00:42

E B via SQLMonster.com (u6539@uwe) writes:
> Somebody have any ideas ????????????

I guess no one answer because there was not much information to work from.
But I'lll give it a try.

>>I use Sql server 2000.
>>
>>How can i copy entire table with data from one database to another (the
>>databases are on the same server).
>>
>>I tried to use DTS but because of large amount of data this process
>>taken a lot of time and also the log is growed up.
>>
>>Is there some thing wihout logging like Bulk Insert, only i need from
>>table to table.
>>
>>Also i tried to copy data to flat file and after this to other database
>>but it's rework

There is no way to turn off logging, as an interrupted non-logged operation
would mean that the database would be kapoot.

There is a minimlally logged operation SELECT INTO, which also creates
the table. It's minimally logged if your database is in BULK_LOGGED
recovery. (And I think SIMPLE.)

Another alternative is to perform an INSERT in batches:

WHILE 1 = 1
BEGIN
INSERT target (....)
SELECT TOP 50000 *
FROM source
WHERE NOT EXISTS (SELECT .... FROM target WHERE ...)
IF @@rowcount < 50000
BREAK
END

For this to be meaningful, the database should be in SIMPLE recovery
mode, so that the transaction log is truncated after each batch. (Or
if you are running in FULL or BULK_LOGGED mode, you would need to
backup the transaction log as the loop as running.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]