CODEWITHSUNDEEP
sql-server
user10
user10

Reputation: 167

Sql Server Insert values into table based on existing rows

I have the tables tbl1 and tbl2, as shown below:

tbl1:

ID      Date1        Date2
1       2020-05-11   2020-09-14
1       2020-06-13   2020-09-14
1       2020-07-15   2020-09-14
2       2020-10-01   2020-09-14

tbl1:

ID      Date1        Date2       ID2
 1     2020-05-11    NULL        15
 1     2020-06-13    2020-10-01  15
 1     2020-04-10    NULL        15

I need to insert rows into tbl2 from tbl1 if the ID and Date1 don't already exist in tbl2. My output needs to look like: tbl2:

ID      Date1        Date2      ID2
 1     2020-05-11    NULL       15
 1     2020-06-13    2020-10-01 15
 1     2020-04-10    NULL       15
 1     2020-07-15    2020-09-14 15
 2     2020-10-01    2020-09-14 15

I have tried using except operator, but it doesn't work. What's the best way to do this?

Upvotes: 0

Views: 29

Answers (1)

GMB
GMB

Reputation: 222462

Use not exists:

insert into tbl2 (id, date1, date2, id2)
select t1.id, t1.date1, t1.date2, 15
from tbl1 t1
where not exists (select 1 from tbl2 t2 where t2.id = t1.id and t2.date1 = t1.date1)

I am unclear about what you want with id2, so the above query hardcodes value 15. Maybe you want something dynamic based on available values in the target table - if so:

insert into tbl2 (id, date1, date2, id2)
select t1.id, t1.date1, t1.date2, ti.id2    
from tbl1 t1
cross join (select distinct id2 from tbl1) ti
where not exists (select 1 from tbl2 t2 where t2.id = t1.id and t2.date1 = t1.date1 and t2.id2 = ti.id2)

Upvotes: 1

Related Questions