Copy data between DB with DTS

Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.Lookup Data Driven Query task in SQL BOL. It will allow you to check for the existence of a record on the target server and skip that record when processing. It's a little complex to use, but it works well.

One more caution, since it is a script-driven task, you may experience performance issues with very large record sets.

You may also consider using straight T-SQL and not use DTS at all. Even if the two databases are are separate servers, you can link them and use T-SQL.

Regards,

Hugh Scott

Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.|||You can use query statement to copy the data into db2.

Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.|||Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.

Hi,

i can recommend a tool for this problem:
http://www.sqlscripter.com

Regards
Thomas

Copy Content from Db1 (inconsistent) to Db2

Hi,
the consistency check on my Db1 failed. Though the database seems to be
intact, how ever all tools fail e.g. DTS with error messages.
Does anybody know how to copy the content from Db1 to DB2 (schema is the
same)?
Thanks for any advice
Graham
Graham SmithHi
What is the error you are getting?
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:eJvV8gu3FHA.2640@.TK2MSFTNGP09.phx.gbl...
> Hi,
> the consistency check on my Db1 failed. Though the database seems to be
> intact, how ever all tools fail e.g. DTS with error messages.
> Does anybody know how to copy the content from Db1 to DB2 (schema is the
> same)?
> Thanks for any advice
> Graham
> --
> Graham Smith
>|||insert into db2.owner.tablename
select * from db1.owner.tablename
Or DTS
or backup DB1 and restore on DB2
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Hi,

> insert into db2.owner.tablename
> select * from db1.owner.tablename
thanks a lot. That works great. I have about 30 tables. Do you know how to
script that this statement loops through all user tables?
Thanks in advance
Graham