CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Amit Agarwal
Amit Agarwal

Reputation: 1

How can I return ScopeIdentity in a query of two Insert statements

Here I am inserting values into two columns but now I want to both declared values which is created just now: 

Declare @ClientCompanyId int, @PersonId int

INSERT INTO ClientCompany (CompanyName) 
VALUES ('WebDunia')

SELECT @ClientCompanyId = SCOPE_IDENTITY() 

INSERT INTO Person (ClientCompanyId, Company, AddressLine1, AddressLine2, City, State, Country, Zip, HomePhone, WorkPhone, CellPhone, Fax, EmailId, Website)
VALUES (@ClientCompanyId, 'Google', 'Chicago', 'USA', 'Norway', 'ALASKA', 'CANADA', '12345', 12345678912, 12345, 56454, 'Fax', 'Email', 'Website')
SELECT SCOPE_IDENTITY() AS PersonId

I want PersonId and ClientCompanyId at a time.

Upvotes: 0

Views: 60

Answers (3)

Ricardo Peres
Ricardo Peres

Reputation: 14525

Try this:

INSERT INTO ClientCompany (CompanyName)
OUTPUT Inserted.ID
VALUES ('Google'), ('Microsoft')

It will return a list of all the inserted ids, I'm assuming your id column is ID.

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8487

Use another variable set, same as you use for @ClientCompanyId

Declare @ClientCompanyId int,
        @PersonId int
 INSERT INTO ClientCompany(CompanyName) VALUES     ('Google')
 SELECT @ClientCompanyId=SCOPE_IDENTITY() 

Insert into Person (ClientCompanyId,Company,AddressLine1, AddressLine2, 
                    City, State, Country, Zip, HomePhone, WorkPhone, 
                    CellPhone, Fax, EmailId, Website)                                  
Values(@ClientCompanyId,'Google','Chicago','USA','Norway',             
      'ALASKA','CANADA','12345',12345678912,12345,56454,'Fax',
       'Email','Website')

SELECT @ClientCompanyId, @PersonId=SCOPE_IDENTITY()     <<<<<<<<<<<<<<<<<**Here**

Upvotes: 2

NDJ
NDJ

Reputation: 5194

why not just replicate what you do with @ClientCompanyID with @PersonID after the 2nd insert?

i.e. the line SELECT SCOPE_IDENTITY() as PersonId should read SELECT @PersonID = SCOPE_IDENTITY() Then both variables will have values set.

I don't know in which context this sql runs, but if you need both values out, you could either select them - or in the case of a stored procedure have them as out parameters.

Upvotes: 1

Related Questions