Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
donpolix
Yak Posting Veteran
97 Posts |
 Posted - 2005-11-13 : 14:35:18
|
| Hi gurus! I am inserting data to table x from table y. I only need to add rows from table y that do not exist in table x. They are unique by field 1 (f1) and field 2 (f2) combined. My sql looks awkward, Im getting the results I wanted tho. But I read somewhere, it's not a good approach to concat the columns for that purpose. Please advise on what is the better way to do it.--sample table x...create table x (f1 char(1), f2 char(1))insert into x select 'a', 'b'insert into x select 'c', 'd'insert into x select 'e', 'f'--sample table y...create table y (f1 char(1), f2 char(1))insert into y select 'a', 'b'insert into y select 'a', 'x'insert into y select 'c', 'z'--to insert the pair from table y not existing in table x.--(bad sql script...)insert into xselect f1, f2 from y where f1+f2 not in (select f1+f2 from x)Thanks!DonnDonn Policarpio |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-11-13 : 14:39:37
|
| look up "not exists" in BOL. you can then join on these two columns instead of concatenating them |
 |
|
|
donpolix
Yak Posting Veteran
97 Posts |
Posted - 2005-11-13 : 14:53:45
|
| im sorry, i can't get it to work on pair of columns..a little help please.Donn Policarpio |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-13 : 14:59:14
|
| select *from t1where not exists (select * from t2 where t1,col1 = t2.col2 and t1.col3 = t2.col4select t1.*from t1left join t2on t1.col1 = t2.col2and t1.col3 = t2.col4where t2.col2 is null==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
donpolix
Yak Posting Veteran
97 Posts |
Posted - 2005-11-13 : 15:32:15
|
 thanks guys!Donn Policarpio |
|
|
|
|
|
|
|
|
Get column pair not existing on another table