Re: How copy table data from one database to another

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 tabl
e
>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

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.
 

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

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

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

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 fo
r
sure LOG is growed up and it's taken a lot of time something like  1700000 i
n
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

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

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 fo
r
sure LOG is growed up and it's taken a lot of time something like  1700000 i
n
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 f
or
>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

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