|
|
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
Navigation:
[Reply to this message]
|