Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

How can i create a record and find its id to create more records in other tables all at the same time?

I have 2 tables.

employee
employee_cell

When i insert an employee a employeeid is created, how can i get that key and insert something into employee_cell all at the same time?




0
mmedi005
Asked:
mmedi005
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:

you have to enable the identity property for EmployeeID  after the first insert, the last inserted EmployeeID can be retrieved using SCOPE_IDENTITY()

DECLARE @LastEmpID int
INSERT INTO Employee
SELECT ....
SELECT @LastEmpID = scope_identity()
INSERT INTO Employee_Cell (EmployeeID, ... )
select @LastEmpID, ....
0
 
DxpertCommented:
Use the @@IDENTITY system function.

http://msdn.microsoft.com/en-us/library/ms187342.aspx
0
 
DxpertCommented:
Oops.. I guess SCOPE_IDENTITY would do a better job since it works within the same scope  :-)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mmedi005Author Commented:
>>you have to enable the identity property for EmployeeID  after the first insert, the last inserted EmployeeID can be retrieved using SCOPE_IDENTITY()

DECLARE @LastEmpID int
INSERT INTO Employee
SELECT ....
SELECT @LastEmpID = scope_identity()
INSERT INTO Employee_Cell (EmployeeID, ... )
select @LastEmpID, ....

so will this work?

INSERT INTO employee(john,doe)VALUES(first,last)
@lastID = scope_identity()
INSERT INTO employee_cell(@lastID,555-5555)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if there is an identity column on the Employee table thhis will work

INSERT INTO employee(first,last)VALUES('john','doe')
SELECT @lastID = scope_identity()
INSERT INTO employee_cell
SELECT @lastID,'555-5555'
0
 
mmedi005Author Commented:
makes sense....

why this in the second insert?
INSERT INTO employee_cell
SELECT @lastID,'555-5555'
0
 
mmedi005Author Commented:
this is what i have set for employeeid

Identity Specification    Yes
      (Is Identity)             Yes
      Identity Increment   1
      Identity Seed          1


are these settings correct to use scope_identity()?
0
 
chapmandewCommented:
Yes, it means it will start the seeding at 1 and increment by 1 for each row entered.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now