output clause with duplicate key issue

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

output clause with duplicate key issue

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-09-20 : 06:09:04

Hi,
I have a query like

UPDATE AdhocTelephone SET InsertDate =@CurDate
OUTPUT Inserted.Telephone INTO TelephonesCollection (Telephone)
WHERE
insertDate is null

in the above query when the telephone number that i am trying to insert from AdhocTelephone already presents in TelephonesCollection i am getting error because of the primary key violation.

is it possible to check the value existance in OUTPUT clause before inserting?

thanks
subha

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-20 : 06:29:19

Subetha,

You can add an "if Statement"

if not exists (select 'x' from TelephonesCollection where Telephone in (select Telephone from AdhocTelephone ) )
begin

UPDATE AdhocTelephone SET InsertDate =@CurDate
OUTPUT Inserted.Telephone INTO TelephonesCollection (Telephone)
WHERE
insertDate is null

End

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 11:53:24

quote:
Originally posted by senthil_nagore

Subetha,

You can add an "if Statement"

if not exists (select 'x' from TelephonesCollection where Telephone in (select Telephone from AdhocTelephone ) )
begin

UPDATE AdhocTelephone SET InsertDate =@CurDate
OUTPUT Inserted.Telephone INTO TelephonesCollection (Telephone)
WHERE
insertDate is null

End

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

please keep in mind that in case the value is already there in TelephonesCollection table, it will skip update action as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 13:08:53

As far as I know, there is not a direct way to use the output clause to insert or output a subset of the INSERTED table. You can of course, insert into a temp table and merge with your Telephones collection, or wrap your current query into a cte, output into a table variable and use that table variable to merge into TelephonesCollection table etc. But any of those methods make a simple construct into a complicated piece of work.

Also, there is some quirky behavior regarding the data returned by the OUTPUT clause, which behaves differently based on whether you are on SQL 2005 or SQL 2008. See here: https://connect.microsoft.com/SQLServer/feedback/details/642177/output-clause-not-working-on-sql-2008-2008r2-as-mentioned-but-working-on-2005-when-insert-fails-output-has-nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources