How to add data to two tables linked via a foreign key?

Question

If I were to have 2 tables, call them TableA and TableB. TableB contains a foreign key which refers to TableA. I now need to add data to both TableA and TableB for a given scenario. To do this I first have to insert data in TableA then find and retrieve TableA's last inserted primary key and use it as the foreign key value in TableB. I then insert values in TableB. This seems lika a bit to much of work just to insert 1 set of data. How else can I achieve this? If possible please provide me with SQL statements for SQL Server 2005.

Answer 1

That sounds about right. Note that you can use SCOPE_IDENTITY() on a per-row basis, or you can do set-based operations if you use the INSERT/OUTPUT syntax, and then join the the set of output from the first insert - for example, here we only have 1 INSERT (each) into the "real" tables:

/*DROP TABLE STAGE_A
DROP TABLE STAGE_B
DROP TABLE B
DROP TABLE A*/
SET NOCOUNT ON

CREATE TABLE STAGE_A (
    CustomerKey varchar(10),
    Name varchar(100))
CREATE TABLE STAGE_B (
    CustomerKey varchar(10),
    OrderNumber varchar(100))

CREATE TABLE A (
    Id int NOT NULL IDENTITY(51,1) PRIMARY KEY,
    CustomerKey varchar(10),
    Name varchar(100))
CREATE TABLE B (
    Id int NOT NULL IDENTITY(1123,1) PRIMARY KEY,
    CustomerId int,
    OrderNumber varchar(100))

ALTER TABLE B ADD FOREIGN KEY (CustomerId) REFERENCES A(Id);

INSERT STAGE_A VALUES ('foo', 'Foo Corp')
INSERT STAGE_A VALUES ('bar', 'Bar Industries')
INSERT STAGE_B VALUES ('foo', '12345')
INSERT STAGE_B VALUES ('foo', '23456')
INSERT STAGE_B VALUES ('bar', '34567')

DECLARE @CustMap TABLE (CustomerKey varchar(10), Id int NOT NULL)

INSERT A (CustomerKey, Name)
OUTPUT INSERTED.CustomerKey,INSERTED.Id INTO @CustMap
SELECT CustomerKey, Name
FROM STAGE_A

INSERT B (CustomerId, OrderNumber)
SELECT map.Id, b.OrderNumber
FROM STAGE_B b
INNER JOIN @CustMap map ON map.CustomerKey = b.CustomerKey

SELECT * FROM A
SELECT * FROM B

Answer 2

If you work directly with SQL you have the right solution.

In case you're performing the insert from code, you may have higher level structures that help you achieve this (LINQ, Django Models, etc).

Answer 3

If you are going to do this in direct SQL, I suggest creating a stored procedure that takes all of the data as parameters, then performs the insert/select identity/insert steps inside a transaction. Even though the process is still the same as your manual inserts, using the stored procedure will allow you to more easily use it from your code. As @Rax mentions, you may also be able to use an ORM to get similar functionality.