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.
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.
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.
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.
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
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With