Copying only the records that don't already exist in destination table.

[dron]

Hello,

What SQL query should I use, in order to copy records from one table to another, both of the same schemas, in such way that only records that don’t already exist in the destination table will be copied?

For example:

Table A – Destination table
******
Col1 Col2 Col3
**** **** ****
Aaa 111 !!!
Bbb 222 @@@
Ccc 333 ###
Ddd 444 $$$
Eee 555 %%%

Table B – Source table
******
Col1 Col2 Col3
**** **** ****
Bbb 222 @@@
Ddd 444 $$$
Fff 666 ^^^
Ggg 777 &&&

And the result should be:
Table A
******
Col1 Col2 Col3
**** **** ****
Aaa 111 !!!
Bbb 222 @@@
Ccc 333 ###
Ddd 444 $$$
Eee 555 %%%
Fff 666 ^^^
Ggg 777 &&&

Thanks.

[Ramy Mahrous]

If you col1 not primary key you can use trigger to check on the columns in inserted table(read about it in triggers) if col1 data already exists don't insert it in the destination table.

[atinobrian]

assuming that the col1 is having unique constraint in both the tables

insert into Table A(col1,col2,col3)
select TableB.col1,TableB.col2,TableB.col3 from Table B left outer Join Table A on 
Table B.col1 = Table A.col1 where TableA.col1 is null

[dron]

Hi Atinobrian,

Thank you for the reply.
And what should I do if both Col1 and Col2 are the key of the table (not only Col1)?
I mean that the values of Col1 can be repeated, as long as the values of Col2 of the corresponding rows are different.

[atinobrian]

Here is the code

insert into Table A(col1,col2,col3)
select TableB.col1,TableB.col2,TableB.col3 from Table B left outer Join Table A on 
Table B.col1 = Table A.col1 and Table B.col2 = Table A.col2
where TableA.col1 is null or TableA.col2 is null

[dron]

Thank you very much!

[atinobrian]

please mark it solved

[dron]

Already did.

[johnny.g]

Here is the code

insert into Table A(col1,col2,col3)
select TableB.col1,TableB.col2,TableB.col3 from Table B left outer Join Table A on 
Table B.col1 = Table A.col1 and Table B.col2 = Table A.col2
where TableA.col1 is null or TableA.col2 is null

hey dude,,,this was a very nice code,,my half problems are solved because of this...can u provide me the same type of code for updating...the scenario is the same....hope u got it,,,

Thnks in advance...

[atinobrian]

well its a little difficult to understand, If its not a hassle can you please provide me with an example , would be much more clear :)

[johnny.g]

well its a little difficult to understand, If its not a hassle can you please provide me with an example , would be much more clear :)

here is an example:::
Table1
KEY X Y Z
---------- ---------- ---------- ----------
1 100 100 100
2 100 100 100
3 100 100 100
4 100 100 100
5 100 100 100

___________________________________
Table 2
KEY X Y Z
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
4 100 100 100
5 100 100 100
6 121 11 12
___________________________________

Here i have two tables Table1 and Table2. I want table1 to be updated based on the records of table 2. that means in this case key 1, 2 and 3 of table 1 will be updated as per table 2.. Also I dont have any primary key in my tables.. So here the output will be like this

Table 1
Table1
KEY X Y Z
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
4 100 100 100
5 100 100 100
6 121 11 12

___________________________________________

Hope u get wht i am trying to say..
thnks in advance..

[atinobrian]

hmm since there is no primary key in your table
it would be tough to update the records, in the original case there was a composite key so it was easy to find and insert unique records
In any case you should have atleast a primary key or a composite key so that the update can be performed

[dron]

Why not to break that task into two separate queries? The first one will UPDATE the already existing keys in Table1 based on the corresponding keys in Table2, and the second one will INSERT the keys of Table2 that don't exist in Table1?
By "keys" I mean rows with specific keys.

[johnny.g]

Why not to break that task into two separate queries? The first one will UPDATE the already existing keys in Table1 based on the corresponding keys in Table2, and the second one will INSERT the keys of Table2 that don't exist in Table1?
By "keys" I mean rows with specific keys.

thnks,,i am done with the insert command,,can u provide me with the update command for the same scenario...
Waiting for ur reply...
Thnks in advance

[atinobrian]

Ohh if that is the case then here is the update query,

update Table1
set Table1.X = Table2.X,
      Table1.Y = Table2.Y,
      Table1.Z = Table2.Z
from Table2 inner join Table1 on
Table2.KEY = Table1.Key
where Table1.x <> Table2.x and Table1.y <> Table2.Y 
and Table1.Z <> Table2.Z

This is the basic update code, now you can change the 'and' and 'Or' Placements in the where clause depending upon your choice :)

In your Senario it seems all the table 2 records are there in table 1 so the above query would become

update Table1
set Table1.X = Table2.X,
      Table1.Y = Table2.Y,
      Table1.Z = Table2.Z
from Table2 inner join Table1 on
Table2.KEY = Table1.Key

Hope it helps

[johnny.g]

Ohh if that is the case then here is the update query,

update Table1
set Table1.X = Table2.X,
      Table1.Y = Table2.Y,
      Table1.Z = Table2.Z
from Table2 inner join Table1 on
Table2.KEY = Table1.Key
where Table1.x <> Table2.x and Table1.y <> Table2.Y 
and Table1.Z <> Table2.Z

mind blowing,,, thnks dude,,thnks a lot,,u solvd my problem....it works...
Thnks

This is the basic update code, now you can change the 'and' and 'Or' Placements in the where clause depending upon your choice :)

In your Senario it seems all the table 2 records are there in table 1 so the above query would become

update Table1
set Table1.X = Table2.X,
      Table1.Y = Table2.Y,
      Table1.Z = Table2.Z
from Table2 inner join Table1 on
Table2.KEY = Table1.Key

Hope it helps

Thnks dude,,it works,,u solved my query,,was trying this one from the past couple of days,,now its done...thnks to u

[johnny.g]

hey that query is done,,, got some other problem..i have installed sql server 2008 in my system...but i am not able to connect to the report server... i have configured the reporting server but still i m facing the problem...can u help me in this case...

thnks in adv