Posted by E B on 11/24/05 17:29

TNX Erland,

However i think because of
> WHERE NOT EXISTS (SELECT .... FROM target WHERE ...)

it will also take a lot of time.

All that i need to do is to copy entire table with it's structure to other
database on the same server

i have 10 tables with aproximatly 3000000 rows, while i tried to use DTS for
sure LOG is growed up and it's taken a lot of time something like 1700000 in
a hour . (In the target table i don't have any constrints and indexes).

Thank Erland


Erland Sommarskog wrote:
>> 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.
>>>
>[quoted text clipped - 9 lines]
>>>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.
>

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200511/1

[Back to original message]