INSERT IF NOT EXISTS - 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

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT IF NOT EXISTS

Author

Topic

diogenes
Starting Member

6 Posts

Posted - 2011-06-01 : 10:15:21

Hi,
I have these two following tables:

[Registry]

pin Date
1254 3-Feb-2011
1254 4-Feb-2011
1254 5-Feb-2011
1111 4-Feb-2011
1111 5-Feb-2011

[Rule]

Date
3-Feb-2011
4-Feb-2011
5-Feb-2011
6-Feb-2011

I need to insert into the Registry table, the date that is missing for each distinct pin, based on the Rule Table.

I need to get the result as:

[Registry]

pin Date
1254 3-Feb-2011
1254 4-Feb-2011
1254 5-Feb-2011

1254 6-Feb-2011 // Iserted

1111 3-Feb-2011 // Inserted
1111 4-Feb-2011
1111 5-Feb-2011
1111 5-Feb-2011 //Inserted

Does Anyone know how to to this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-01 : 10:24:04

insert registry
select re.pin, ru.date
(select distinct pin from registry) re
cross join Rule ru
left join registry r
on re.pin = r.pin
and ru.date = r.date
where r.pin is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

diogenes
Starting Member

6 Posts

Posted - 2011-06-01 : 10:42:12

I Got Syntax error Next (select distinct pin from registry)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 10:45:49

insert registry
select re.pin, ru.date
from
(select distinct pin from registry) re
cross join Rule ru
left join registry r
on re.pin = r.pin
and ru.date = r.date
where r.pin is null

Jim

Everyday I learn something that somebody else already knew

diogenes
Starting Member

6 Posts

Posted - 2011-06-01 : 10:57:54

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources