Solved

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

Posted on 2008-06-26
8
191 Views
Last Modified: 2010-03-20
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
Comment
Question by:mmedi005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21876131

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
 
LVL 10

Expert Comment

by:Dxpert
ID: 21876140
Use the @@IDENTITY system function.

http://msdn.microsoft.com/en-us/library/ms187342.aspx
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 21876183
Oops.. I guess SCOPE_IDENTITY would do a better job since it works within the same scope  :-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mmedi005
ID: 21876248
>>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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 21876297
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
 

Author Comment

by:mmedi005
ID: 21876342
makes sense....

why this in the second insert?
INSERT INTO employee_cell
SELECT @lastID,'555-5555'
0
 

Author Comment

by:mmedi005
ID: 21876389
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21876735
Yes, it means it will start the seeding at 1 and increment by 1 for each row entered.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question