Posted by Erland Sommarskog on 11/24/05 23:35

E B (u6539@uwe) writes:
> However i think because of
>> WHERE NOT EXISTS (SELECT .... FROM target WHERE ...)
>
> it will also take a lot of time.

A variation is to do:

SELECT @key = MIN (col) FROM source
SELECT @incr = 50000
WHILE @key IS NOT NULL
BEGIN
INSERT target (...)
SELECT ... FROM source WHERE col BETWEEN @key AND @key + @incr
SELECT @key = MIN(col) FROM source WHERE col > @key + @incr
END

col should here be the first column in the clustered index. It does not
have to be a unique column, or a numeric column. For instance, it could
very well be a datetime column, and you could copy data for one month at
time or whatever that makes a reasonable batch size.

> 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).

1.7 millions rows in another? That's slow. I don't know DTS, so I cannot
comment.

The other alternative is to bulk out the data, and then use BCP. This
may be faster, but it is more complex, with more risk for errors.

--
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]