Posted by E B via SQLMonster.com on 11/27/05 12:40

Thanks Erland, actually i just saw your post to my question,
your answer is more then satisfy me.


As always i find myself in situation " why i didn't think about it ".

Thank U.

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

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