transfer rows from table A to table B that only if they are not in B? 1

[joelwenzel]

I want to create a DTS package that transfers data from table A to table B. However, table B might already have data from table A in it. I only want unique values in table B (and A).

So, is there some way to only copy over records from table A that are not already present in table B

 

[mrdenny]

Like this.

select *
from tablea
where tablea.id not in (select id from tableb)

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[joelwenzel]

oh yeah, I tried that but there is one additional condition, Table B is in a different database which is potentially on a different machine. If it is on the same machine, I think I could use something like

DBName.dbo.tableb

 

[mrdenny]

Correct, if it's in a different database you need to use the three part name.

If it is on a different server setup a linked server from your box to the remote SQL Server then use the four part name to pull the data.

select *
from tablea
where tablea.id not in (select id from ServerName.DBName.dbo.tableb)

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[joelwenzel]

oh cool. I didn't know about the four part name. Thanks a lot

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief