Append Unmatched Record - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Append Unmatched Record

Author

Topic

PeeJay2583
Starting Member

40 Posts

Posted - 2011-07-18 : 06:51:46

I have two tables, tb1 and tb2 i wanted to append data from tb2 into tb1 only if the record doesn't match in the tb1.

Example: I have a field "Work_id" in tb1 with numerous records and I only want to append that data in tb1 from tb2 which tb1 do not have .

Tb1
Work_id Productive
23 6
21 8
24 7.5

Tb2
Work_id Productive
25 7
28 8
21 8.5

I want tb2 row # 3 should not be appended in tb1 as we already have work_id 21 in tb1.

Any help would be appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 06:56:09

how do you determine that record "21 8.5" in Tb2 is not in Tb1 ?

KH
[spoiler]Time is always against us[/spoiler]

PeeJay2583
Starting Member

40 Posts

Posted - 2011-07-18 : 07:01:08

Work_id and Productive are 2 separate fields.

21 is occuring in tb1 and tb2 aswell so therefore i do not want 21 work_id in tb1 from tb2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 07:10:31

[code]
insert into tb1 (work_id, productive)
select work_id, productive
from tb2 t2
where not exists
(
select *
from tb1 x
where x.work_id = t2.work_id
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

PeeJay2583
Starting Member

40 Posts

Posted - 2011-07-18 : 07:21:47

Great that worked!

Another thing I wanted to do here is Work_id 21 has productive time as 8 in TB1 and work_id 21 has productive time as 8.5 in tb2.

Can I also add that difference of .5 in tb1 using the same query.

I mean if work_id matches in 2 tables it should give the productive time of tb2 instead of tb1.

Mukesh Sajjan

PeeJay2583
Starting Member

40 Posts

Posted - 2011-07-18 : 07:21:48

PeeJay2583
Starting Member

40 Posts

Posted - 2011-07-18 : 07:21:56

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 07:51:17

this is what you want ?


update tb1
set    productive = tb1.productive + tb2.productive
from   tb1 inner join tb2 on tb1.work_id = tb2.work_id

KH
[spoiler]Time is always against us[/spoiler]

PeeJay2583
Starting Member

40 Posts

Posted - 2011-07-18 : 08:44:04

Thanks for ur help!

Much Appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources