Compare Values In A Simple Transfer 1

[Nemiz]

If I have 2 Connections - A Source and a Destination - which both have the same table (Table1) and I want to transfer values from the Source.Table1 that aren't already in the Destination.Table1, how do I do that?

Something like:

select * from Source.Table1 where Source.Table1.PrimaryKey not in (select Destination.Table1.PrimaryKey from Destination.Table1)

I've tried everything, I can think of

 

[fluteplr]

If you run the above query in query analyser does it retrieve the records you are expecting to see?

 

[Nemiz]

No, the query is just an example of, what I want to do. I don't think it is possible to use to connections (servers/databases) at the same time in the Query Analyzer, but I have dont it in my "query", because it shows what I want the DTS to do. If you run the exact same query on two tables located in the same database, it would return, what I need. I just need the comparison done between a table on the destination and one on the source, and I need it done in DTS (preferably in the DTS designer).

 

[Nemiz]

Come on people! Please help me...

My mind just can't comprehend, why it isn't possible to do a simple compare, before inserting via DTS. Picture the following:

1. You have created a simple DTS package that transfers data between to complety alike databases. One or more tables have primary keys.
2. In one of the databases there is dynamically inserted data, whilst the other is initially empty and only gets data, when running the DTS package. The DTS package is to be run at certain intervals to update the second database with eventual new data from the first database (yes, it sounds like replication, but it isn't).
3. The first transfer will succeed, because the destination database is empty.
4. BUT if a second transfer is made at a later time, after new data has been inserted into the first database, the transfer will fail, because some of the data from the first database is already in the second database. You can't insert the same primary key twice.
5. Therefore you need to compare somehow either before extracting the values to transfer from the source database, or before inserting them in the destination database.

My question is: How do you do this?

 

[MDXer]

I haven't tested it so I'm not 100% but it seems using linked servers something like the following would be possible used in an execute sql Tas, and would only require 1 Connection Object.

Insert Into ServerB.DB1.dbo.Table1
(Column1,Column2,Column3)

select Column1, Column2, Column3
from ServerA.DB1.dbo.Table1
where Column1 not in (
select Column1
from ServerB.DB1.dbo.Table1)


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Nemiz]

Thanks for your help. I was hoping to be able to use the graphical tools in DTS Designer, instead of "clean" SQL, but your suggestion does exactly what I need.

)
Nemiz