Inserting into a table using select query no dup

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)

Inserting into a table using select query no dup

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-08-19 : 14:59:27

Hi,

I have a table called facilitysequence it has the following columns. Facilityid PK, Type, Number.

Now, I want to insert the data from the facility table into the facilitysequence but facilitysequence also contains some facilityid. So, the query is killing on duplicate error.

insert into facilitysequence(facilityid,type,number)
select facilityid,'count', 0
from facility

Mohammad Azam
www.azamsharp.net

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-19 : 15:12:06

Use NOT EXISTS like below...

insert into facilitysequence(facilityid,type,number) 
select facilityid,'count', 0 from facility
where not exists(select * from facilitysequence where facilitysequence.facilityid = facility.facilityid )

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-20 : 00:21:24

insert into facilitysequence(facilityid,type,number)
select facilityid,'count', 0 from facility f
left join facilitysequence fs on fs.facilityid = f.facilityid
where fs.facilityid is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources