如何将数据添加到通过外键链接的两个表？

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.

如果我有2个表，请将它们称为TableA和TableB。 TableB包含引用TableA的外键。我现在需要为给定方案向TableA和TableB添加数据。为此，我首先必须在TableA中插入数据，然后查找并检索TableA的最后插入的主键，并将其用作TableB中的外键值。然后我在TableB中插入值。这似乎只是为了插入1组数据而做了很多工作。我怎么能做到这一点？如果可能的话，请为我提供SQL Server 2005的SQL语句。

3 个解决方案

#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:

这听起来是正确的。请注意，您可以逐行使用SCOPE_IDENTITY（），或者如果使用INSERT / OUTPUT语法，则可以执行基于集合的操作，然后加入第一个插入的输出集 - 例如，这里我们只有1个INSERT（每个）进入“真实”表：

/*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

#2

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

如果直接使用SQL，则可以使用正确的解决方案。

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

如果您从代码执行插入，您可能有更高级别的结构，可以帮助您实现这一点（LINQ，Django模型等）。

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

如果要在直接SQL中执行此操作，我建议创建一个存储过程，将所有数据作为参数，然后在事务内执行插入/选择标识/插入步骤。即使该过程仍与手动插入相同，使用存储过程也可以让您更轻松地从代码中使用它。正如@Rax所提到的，您也可以使用ORM来获得类似的功能。