In data Insert script how to put parent record'

问题:

I m creating a database insert script which involve a parent and a child table.

Parent record's auto-generated id will be inserted to child record as reference key.

Question is I don't know in script how to tell SQL server to use inserted parent records's id in child table record as reference. Please help me how to put parent id in child insert statement.

回答1:

At the top of your code do this:

DECLARE @parent_id int

Then, immediately after you do the insert into the parent table, do this:

SELECT @parent_id = SCOPE_IDENTITY()

That retrieves the id of the most recent insert into the parent table.

Finally, in your insert into the child table, do something like this:

INSERT INTO child_table (parent_id, colA, colB, colC) SELECT @parent_id, valueA, valueB, valueC;

(I just made up valueA, etc.--you know what your insert into the child will look like, just make sure to match up parent_id with @parent_id.

回答2:

In case you have bulk inserts in your table you can grab inserted ids directly in insert statement and then use those values for further processing of child entities. For example:

Declare @out Table(id int, some_col int)

Insert Into SomeTable(some_col)
Output inserted.id, inserted.some_col Into @out
Values(5),(6)

Now you have in your table variable inserted identities along with some other column values if you need.

In case of seperate inserts one by one, you can also use Scope_Identity or @@Identity depending on your logic.

回答3:

may be this works fine

CREATE TABLE PARENT
(
 Pid int primary key identity(1,1)
 ,Pdescription varchar(100)
)

CREATE TABLE Child

(
ChildId int primary key identity(1,1)
,PidcHILD int foreign key references parent(pid )

)
CREATE TABLE #TEMPChild

(
PidcHILD int 
)


BEGIN TRAN
INSERT INTO PARENT 
OUTPUT inserted.Pid 
INTO #TEMPChild(PidcHILD)
VALUES('Record1');

INSERT INTO Child(PidcHILD)-- Add other columns here for child table
SELECT PidcHILD FROM #TEMPChild

COMMIT TRAN 

SELECT * FROM Child
SELECT * FROM  PARENT

回答4:

step-1: get max(pid) form Parent table. step-2: store in variable. step-3: insert this variable into child table .