How to insert a new record (and get it's identity), if a record is not found

What I'm trying to do is write a query (actually it will be a sub-query) that gets the ID of a record using a standard SELECT/WHERE, but if the record is not found then I want to insert a record and return the ID of the inserted record.

So far I've got this (which doesn't work!)

SELECT ISNULL((Select ContactID AS ID FROM [TileManager].[dbo].[Contact] WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs')), (INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs'); SELECT @.@.IDENTITY AS ID))

Any help would be greatly appreciated.

Rob:

Be careful using the @.@.identity function; try SCOPE_IDENTITY() instead. Look it up in the books.

Dave

|||

I don't believe you can embedded the insert statement like that - why aren't you trying something like this

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')
SELECT ID = @.@.IDENTITY

|||

SELECT ID = @.@.identity will almost always work; it can experience problems such as:

-- -
-- Illustrate the difference between @.@.identity and scopy_identity()
--
-- In this example, @.@.identity returns the value of the identity
-- that was inserted by the trigger
-- -
set nocount on

create table dbo.whatA ( what1 integer identity)
go
create table dbo.whatB ( what2 integer identity)
go

insert into whatA default values insert into WhatA default values
insert into whatB default values
insert into whatA default values insert into WhatA default values
insert into whatA default values insert into WhatA default values

--select * from whatA
--select * from whatB
go

create trigger dbo.trI_whatB on whatB
for insert
as
begin
insert into whatA default values
end

go

insert into whatB default values
select @.@.identity as [Incorrect @.@.Identity]
select scope_identity() as [Correct scope_identity()]
select max (what1) as [WhatA Identity] from whatA
select max (what2) as [WhatB Identity] from whatB
go

drop trigger dbo.trI_whatB
go
drop table dbo.whatA
go
drop table dbo.whatB
go

--
-- O U T P U T :
--

-- Incorrect @.@.Identity
-- -
-- 7

-- Correct scope_identity()
-- -
-- 2

-- WhatA Identity
-- --
-- 7

-- WhatB Identity
-- --
-- 2

|||

That's close to what I'm trying to get, but, what I want is the ID of the record if it is found or the ID of the added record if it is not found. The code you've given returns NULL if the record already exists.

Thanks

|||

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')

SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs'

is it acceptable for you to do it as two statements like this?

AWAL

|||

This will do the existance check first and only insert if no record is found...

DECLARE @.ID int

--Get value if existing

SELECT @.ID

FROM [TileManager].[dbo].[Contact]

WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs');

--No value found so insert

IF @.ID IS NULL

BEGIN

INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs');

SELECT @.ID = SCOPE_IDENTITY();

END

--Use ID for something

SELECT @.ID

sql