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 |
|
interclubs
Yak Posting Veteran
63 Posts |
 Posted - 2002-07-31 : 14:27:49
|
| I am running an insert statement, that keeps generating an error because it is violating a PK contraint (inserting a duplicate). Is there anyway to trap the error so the insert continues and doesn't quit? My code is as follows:INSERT emails (email) SELECT email FROM newsletterThanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-31 : 14:54:22
|
| Yes. Change the query so that it doesn't violate the primary key in the first place. |
 |
|
|
MuffinMan
Posting Yak Master
107 Posts |
Posted - 2002-07-31 : 15:26:11
|
Obviously there is a Primary Key on the table for a reason, so attempting to ignore the message undermines the integrity of the table. As Rob says, change the query.Here is one way. The query checks BEFORE it does an insert whether an email address already exists in the emails table.set nocount oncreate table #emails (email varchar(30) not null primary key clustered)insert #emails (email) values ('joe@abc.com')insert #emails (email) values ('mary@abc.com')insert #emails (email) values ('jill@abc.com')insert #emails (email) values ('YakWrangler@sqlteam.com')insert #emails (email) values ('PrimaryKeyViolator@sqlteam.com')create table #newsletter (email varchar(30) not null)insert #newsletter (email) values ('joe@abc.com')insert #newsletter (email) values ('Alex@NewMail.com')insert #newsletter (email) values ('PrimaryKeyViolator@sqlteam.com')/*INSERT #emails (email) SELECT email FROM #newsletter -- this causes an eror */print '**** Data before insert statement ****'select * from #emailsset nocount offprint ''print '**** Do the insert ****'INSERT #emails (email) SELECT distinct n.email FROM #newsletter nwhere not exists (SELECT 1 from #emails e where e.email = n.email)set nocount onprint ''print '**** Data AFTER insert statement ****'select * from #emails-- drop table #emails, #newsletter-- here are the results**** Data before insert statement ****email ------------------------------ jill@abc.comjoe@abc.commary@abc.comPrimaryKeyViolator@sqlteam.comYakWrangler@sqlteam.com **** Do the insert ****(1 row(s) affected) **** Data AFTER insert statement ****email ------------------------------ Alex@NewMail.comjill@abc.comjoe@abc.commary@abc.comPrimaryKeyViolator@sqlteam.comYakWrangler@sqlteam.com |
|
|
|
|
|
|
Ignoring Errors