CODEWITHSUNDEEP
sqlsql-server
Ali.azimi
Ali.azimi

Reputation: 103

How to use insert statement in a select and return new PK value to put as a field of select

I have a code about Insert into a table, for example:

Insert Into MyTable(PropertyName,PropertyID)
values('PropertyName1', (if exists(select 1 from PropertyTable where propertyName='PropertyName1') return null
else
insert into PropertyTable(Name) values('PropertyName1')
return scope_Identity()))

my problem is at there:

insert into PropertyTable(Name) values('PropertyName1')
return scope_Identity()

I need if my row isn't exist in my table, at first insert new value in a table and then return ID for use in above insert. but i don't know how do it?

Upvotes: 1

Views: 74

Answers (1)

Emerson JS
Emerson JS

Reputation: 156

I don't know if your code is completely ok, but a alternative solution for the use of scope_identity is output clause.

Try this and tell me if works:

Insert Into MyTable (PropertyName,PropertyID) values('PropertyName1',
        (if exists(select 1 from PropertyTable where propertyName='PropertyName1') 
                return null
         else
                insert into PropertyTable(Name) 
                output inserted.nameOfYourIDColumn 
                values('PropertyName1')));

Upvotes: 1

Related Questions