Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 767
Draco Avatar asked Jun 25 '09 12:06

Draco


People also ask

How do you insert data into a table that has a foreign key?

If you are inserting data into a dependent table with foreign keys: Each non-null value you insert into a foreign key column must be equal to some value in the corresponding parent key of the parent table. If any column in the foreign key is null, the entire foreign key is considered null.

Can you relate data to multiple tables using a foreign key?

Foreign keys link data in one table to the data in another table. A foreign key column in a table points to a column with unique values in another table (often the primary key column) to create a way of cross-referencing the two tables.

Is it possible to link two tables using two foreign keys?

To select records from tables with Multiple foreign keys, you need JOINs. In the same way, you can select values of the Name and Gender columns from the Employee table and the Name column from the Office table using two LEFT JOIN statements on the lookup table Employee_Office.


2 Answers

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
like image 166
Marc Gravell Avatar answered Sep 21 '22 17:09

Marc Gravell


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).

like image 30
Roee Adler Avatar answered Sep 25 '22 17:09

Roee Adler