Inserting one table's primary key into another table

Question

I have two tables in SQL Server, Appointment and AppointmentDetails.

Appointment table has two columns AppId and CusId.

AppointmentDetail table has AppId, ApDay, Intime, OutTime, EmpId.

Appointment table has AppId as its primary key, and it is set to auto increment. AppointmentDetails table has primary key on (AppId, ApDay).

My problem how get and insert the primary key of the Appointment table to AppointmentDetails table when I am inserting data into Appointment table???

Some comments - Appointment has a 't' in it. Tables can't have two primary keys - they can have a single primary key that contains two columns though. — David Gardiner, Nov 07 '13 at 14:40
I've given you an answer below, but actually would have been better asking for clarification as to how you are inserting etc. Including code examples is best practice. — talegna, Nov 07 '13 at 14:44

score 0 · Answer 1 · answered Nov 07 '13 at 14:42

Here's one method of doing it (with corrected spellings for table names though I accept these are sometimes beyond your control):

DECLARE @insertedId INT;
BEGIN TRANSACTION
    INSERT INTO Appointment(CusId) VALUES(@cusId);
    SET @insertedId = SCOPE_IDENTITY();
COMMIT

BEGIN TRANSACTION
    INSERT INTO
        AppointmentDetails
    SELECT
        AppId = @insertedId 
        ,ApDay = @apDay
        ,Intime = @inTime
        ,OutTime = @outTime
        ,EmpId = @empId
    FROM
        Appointment
COMMIT

Alternatively you could use a trigger, but they're just evil!

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

I think they asking how can they return the new key that was generated to then insert it into the details table.

Check this post out if using JDBC but the idea is the same for all languages: How to get the insert ID in JDBC?

edited May 23 '17 at 12:13

Community

1
1

answered Nov 07 '13 at 14:43

MJSalinas

139
1
9

Inserting one table's primary key into another table

2 Answers2