Microsoft SQL Server forum - Re: How copy table data from one database to another

Author

Re: How copy table data from one database to another

E B via SQLMonster.com

2005-11-23, 7:23 am

Somebody have any ideas ????????????
TNX

E B wrote:
>Hi all !!!
>
>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
>
>Any suggestions appreciated.
>
>TNX in advance.
>

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

Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200511/1

Erland Sommarskog

2005-11-23, 8:24 pm

E B via webservertalk.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.
[color=darkred]

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

angsql

2005-11-24, 7:23 am

I made the change, still I am not getting the result

set nocount on
delete from loop_projectid

while 1=1
Begin
insert into loop_projectid
select top 500 project_id, project_name from pds_project P
where not exists ( select * from loop_projectid L where
P.Project_id=L.Project_id )

declare @rowcount int
select @rowcount=@@rowcount

select @rowcount as rows11

--set @rowcount=@@rowcount

if @rowcount < 500
begin
break
End
end

select * from loop_projectid

..AS

angsql

2005-11-24, 7:23 am

This code works

set nocount on
delete from loop_projectid

while 1=1
Begin
insert into loop_projectid
select top 500 project_id, project_name from pds_project P
where not exists ( select * from loop_projectid L where
P.Project_id=L.Project_id )

if @@rowcount <= 500
begin
break
end
End
select * from loop_projectid

..AS

E B

2005-11-24, 11:23 am

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:
>
>I guess no one answer because there was not much information to work from.
>But I'lll give it a try.
>
>[quoted text clipped - 9 lines]
>
>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 webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200511/1

Erland Sommarskog

2005-11-24, 8:23 pm

E B (u6539@uwe) writes:
> However i think because of
>
> 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

E B via SQLMonster.com

2005-11-27, 7:23 am

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

E B wrote:
>TNX Erland,
>
>However i think because of
>
>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
>
>[quoted text clipped - 30 lines]
>

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

Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200511/1

E B via SQLMonster.com

2005-11-27, 7:23 am

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:
>
>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.
>
>[quoted text clipped - 3 lines]
>
>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 webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200511/1

Sponsored Links