Tables Compare and add 1

[vaichi123]

I need to compare two tables say t1 and t2 on a column called P and add whatever is missing in t1 to P in t1 in one single script. Can someone help me?

 

[SQLDenis]

If i understood correctly

--left join
INSERT INTO t1
SELECT t2.*
FROM t2 
left join t1 on t2.p= t1.p
where t1.pis null

--not exists
INSERT INTO t1
SELECT t2.*
FROM t2 where not exists (select * from t1 where t2.p= t1.p)


--not in
INSERT INTO t1
SELECT t2.*
FROM t2 where p not in(select p from t1 where p is not null)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[vaichi123]

Thanks a lot SqlDenis for your help. Could you give a brief explaination as to how you wrote it? Like i did not understand the three parts of the code. Please excuse me for being so trivial

 

[SQLDenis]

these 3 things do exactly the same thing you can pick which one you want to use
they are listed from best to worst in terms of performance (but lickely will generate the same execution plan anyhow since the optimizer is sooo smart nowadays ;-) )

the first is a left join
the second is using exists
and the third is using not in


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[vaichi123]

In the above, What should i do If i need to add not all the differences in the two tables but only those items that start with letters 'ab%'

 

[SQLDenis]

you add a where clause

WHERE ColumnName like 'ab%'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[vaichi123]

When i finally got to run the query, I am getting this error below as there are two columns in t1 and i am inserting value for only one column.

Insert Error: Column name or number of supplied values does not match table definition.

 

[SQLDenis]

Please post DDL of the tables

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[vaichi123]

t1:
name varchar(50)
title varchar(50)
city varchar(50)
state varchar(50)
building varchar(50)

t2:

name varchar(50)
title varchar(50)
city varchar(50)
state varchar(50)
Business Unit varchar(50)

I need to compare the two tables and insert into t1 all names that are in t2 and start with ab

 

[SQLDenis]

>>I am getting this error below as there are two columns in t1 and i am inserting value for only one column.


mmm i see 5 columns for t1
however you need to supply values for the other columns or you can pass nulls if nulls are accepted

select t2.name,null,null,null,null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[vaichi123]

Thanks a lot for your help!!!! I was able to run this query.

Just for curiosity sake, Is there any other way to do this without passing nulls for eg. temp tables?