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 |
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2008-11-12 : 16:43:12
|
Need to insert records into table1 with records from table2 if they do not already exist (in table1) insert into #table1 (costcode, jdesumqty) where not exists(select jde_cost_code, jde_sum_qtyfrom #table2)Error: Incorrect syntax near the keyword 'where'.In table1 "costcode" is unique and in table2 "jde_cost_code" is unique |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-12 : 16:48:33
|
insert into #table1 (costcode, jdesumqty) select distinct t2.jde_cost_code, t2.jde_sum_qtyfrom #table2 AS t2where not exists (select * from #table1 AS t1 where t1.costcode = t2.jde_cost_code and t1.jdesumqty = t2.jde_sum_qty)The distinct is essential for handling duplicates within #table2 E 12°55'05.63"N 56°04'39.26" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 23:36:58
|
or use left joininsert into #table1 (costcode, jdesumqty) select distinct t2.jde_cost_code, t2.jde_sum_qtyfrom #table2 AS t2left join #table1 AS t1 on t1.costcode = t2.jde_cost_code and t1.jdesumqty = t2.jde_sum_qtywhere t1.costcode is null |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-13 : 05:02:09
|
You could also use EXCEPT to find differences:SELECT *FROM table1 where xxxxxxexcept select *from table2where xxxx |
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2008-11-13 : 08:16:13
|
Thank you guys, they all worked, I was struggling with this .... |
|
|
|
|
|