如何在具有主要外键关系的两个表中插入记录 [英] how to insert record in two tables having primary n foreign key relationship

问题描述

我有两个表..我想使用存储过程通过事务在表中插入记录。

表之间存在主键和外键关系.product_id是一个表的字段。它是主键并自动递增。所以我如何在第二个表格中插入product_id，这是表格的外键..

所以PLZ帮助我..

提前感谢..

I have two tables.. I want to insert records in tables by transaction using stored procedure.
there is primary and foreign key relationship between tables.. product_id is field of one table. it is primary key and auto incremented. so how I insert product_id in second table which is foreign key of table..
So plz help me..
thanks in advance..

推荐答案

首先插入具有主键

的表中，然后插入具有外键的第二个表中。

Insert first into the table having primary key
and then insert in second table having foreign key.

让我们想象一下场景：

1）将记录添加到主表广告获取主键值

Let's imagine this scenario:
1) add record to Main table ad get primary key value

CREATE PROCEDURE InsertToMain
    @<value_list>
AS
BEGIN 
    INSERT INTO MainTable(<field_list>)
    VAULES(<value_list>)

    RETURN @@IDENTITY
END

2）当您向Main表添加值时，您可以向子表添加值



有关详细信息，请参阅：

@ @IDENTITY（Transact-SQL） [ ^ ]

演练：仅使用存储过程（C＃） [ ^ ]

如何：执行返回单个值的存储过程 [ ^ ]

试试。祝你好运！

2) when you added value to Main table, you're able to add values to child table

For further information, please see:
@@IDENTITY (Transact-SQL)[^]
Walkthrough: Using Only Stored Procedures (C#)[^]
How to: Execute a Stored Procedure that Returns a Single Value[^]
Try. Good luck!

CREATE PROCEDURE InsertData
@Param1	int,     --Main Table
@Param2	varchar(10),--Main Table
@Param3	varchar(300),--Child Table
@Param4	char(4)--Child Table
AS
BEGIN
	DECLARE @pkMain int

	INSERT INTO MainTable (Col1, col2)
		VALUES (@Param1, @Param2);

	SELECT
		@pkMain = SCOPE_IDENTITY();

	INSERT INTO ChildTable (col1, col2, col3)
		VALUES (@pkMain, @Param3, @Param4);

END

这篇关于如何在具有主要外键关系的两个表中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！