如何在存储过程中一次插入两个表? [英] How do I insert into two tables all at once in a stored procedure?

问题描述

可能的重复:
如何将数据插入到两个表中同时在 SQL Server 中?

为学校做一个项目，所以任何帮助都会非常感谢！

Doing a project for school so any help would be great thank you!

我有两个表 - 如何插入到两个表中?所以两个表都是链接的.

I have two tables - how do I insert into two tables? So both tables are linked.

第一个表称为Customer，主键称为CID，自动递增

First table is called Customer with primary key called CID that auto increments

CREATE TABLE [dbo].[Customer](
    [CID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](255) NOT NULL,
    [FirstName] [varchar](255) NOT NULL,
    [MiddleName] [varchar](255) NULL,
    [EmailAddress] [varchar](255) NOT NULL,
    [PhoneNumber] [varchar](12) NOT NULL        
 CONSTRAINT [PK__CInforma__C1F8DC5968DD69DC] PRIMARY KEY CLUSTERED 
(

还有一个名为 Employment 的表，它有一个链接到父表的外键

And a second table called Employment that has a foreign key linked to the parent table

CREATE TABLE [dbo].[Employment](
    [EID] [int] IDENTITY(1,1) NOT NULL,
    [CID] [int] NOT NULL,
    [Employer] [varchar](255) NOT NULL, 
    [Occupation] [varchar](255) NOT NULL,
    [Income] [varchar](25) NOT NULL,
    [WPhone] [varchar](12) NOT NULL,
 CONSTRAINT [PK__Employme__C190170BC7827524] PRIMARY KEY CLUSTERED 
(

推荐答案

你需要做这样的事情:

DECLARE @NewID INT

INSERT INTO Customer(LastName,FirstName,......) VALUES(Value1, Value2, .....)

SELECT @NewID = SCOPE_IDENTITY()

INSERT INTO Employment(CID,Employer,.....) VALUES(@NewID, ValueA,..........)

SCOPE_IDENTITY: 返回最后一个插入到同一范围内的标识列中的标识值.作用域是一个模块:存储过程、触发器、函数或批处理.因此，如果两个语句在同一个存储过程、函数或批处理中，则它们在同一范围内.

SCOPE_IDENTITY: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

这篇关于如何在存储过程中一次插入两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！