sqlserver-如何将主键值插入到另一个表主键中

Question

我有一个存储过程，它将执行插入2个表的操作，第一个表主键将作为地址id插入地址表（将与其他5个表共享），当我尝试提取特定驱动程序、学生的联系信息时，我将引用该地址id。。等正如人们可能说的Scope_Identity，如果我只有两个表，这很好，但当我试图插入表学生或驱动程序的地址Scope_Intity时，我可能会遇到重复或主键冲突。学生们，知道如何插入驱动程序吗。。etc地址表中的主键被建议该键将从窗口形式的文本框中插入，或者任何人都可以建议如何进行此操作以及如何改进存储过程吗

下面是我的存储过程，它将失败，因为没有标识列未设置

USE [GlobalReachCare]
GO
/****** Object:  StoredProcedure [dbo].[main_Escort_Insert]    Script Date: 19/04/2014 09:07:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[main_Escort_Insert]
-- Add the parameters for the stored procedure here
@EscortID int,
@FirstName varchar(50),
@Surname varchar(50),
@CrbCheck bit = 0,
@CrbRef varchar(25) = false,
@ExpiryDate date = null,
@Training bit = false,
@TrainingType bit = false,
--@AddressID int,
@Address1 varchar(20),
@Address2 varchar(20)= null,
@Address3 varchar(20) = null,
@City varchar(16),
@PostCode varchar(16),
@Email varchar(50) = null,
@Telephone nvarchar(20),
@DateOfBirth date= null,
@NationalIN nvarchar(25)
AS
BEGIN
DECLARE @Escort int
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
-- Insert statements for procedure here
Insert Into dbo.tb_EscortsDetails( EscortId,FirstName,Surname, [CRB_Check], [CRB_Ref],ExpiryDate, Training, TrainingType)
                   VALUES(@EscortId,@FirstName,@Surname,@CrbCheck,@CrbRef,@ExpiryDate,@Training,@TrainingType)
                   set @Escort = SCOPE_IDENTITY()

                   insert into tb_Addresses(AddressID,Address1,address2,Address3,City,PostCode,Email,Telephone,DateOfBirth
                   ,NationalINO)
                   Values(@Escort,@Address1,@Address2,@Address3,@City,@PostCode,@Email,@Telephone,@DateOfBirth,@NationalIN)
END

Answer 1

您自己已经给出了答案：

because no identity column is not set

很遗憾，创建表后无法添加标识。试着放下你的桌子，然后重新制作它们。你可以按照

DROP TABLE tb_EscortsDetails
GO
CREATE TABLE tb_EscortsDetails (
    EscortID INT NOT NULL IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    Surname NVARCHAR(50) NOT NULL,
    CRB_Check BIT NOT NULL,
    CRB_Ref NVARCHAR(25) NOT NULL,
    ExpiryDate DATE NOT NULL,
    Training bit NOT NULL,
    TrainingType bit NOT NULL
)
GO
DROP TABLE tb_Addresses 
GO
CREATE TABLE tb_Addresses (
    AddressID INT NOT NULL IDENTITY PRIMARY KEY,
    EscortID INT NOT NULL,
    Address1 NVARCHAR(20) NOT NULL,
    address2 NVARCHAR(20) NOT NULL,
    Address3 NVARCHAR(20) NOT NULL,
    City NVARCHAR(16) NOT NULL,
    PostCode NVARCHAR(16) NOT NULL,
    Email NVARCHAR(50) NOT NULL,
    Telephone NVARCHAR(20) NOT NULL,
    DateOfBirth DATE NOT NULL,
    NationalINO  NVARCHAR(25) NOT NULL,
    FOREIGN KEY (EscortID) REFERENCES tb_EscortsDetails (EscortId)
)

然后你可以执行你的程序

USE [GlobalReachCare]
GO
/****** Object:  StoredProcedure [dbo].[main_Escort_Insert]    Script Date: 19/04/2014 09:07:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[main_Escort_Insert]
    -- Add the parameters for the stored procedure here
    --@EscortID int,
    @FirstName varchar(50),
    @Surname varchar(50),
    @CrbCheck bit = 0,
    @CrbRef varchar(25) = false,
    @ExpiryDate date = null,
    @Training bit = false,
    @TrainingType bit = false,
    --@AddressID int,
    @Address1 varchar(20),
    @Address2 varchar(20)= null,
    @Address3 varchar(20) = null,
    @City varchar(16),
    @PostCode varchar(16),
    @Email varchar(50) = null,
    @Telephone nvarchar(20),
    @DateOfBirth date= null,
    @NationalIN nvarchar(25)
AS
BEGIN
    DECLARE @Escort int
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    Insert Into dbo.tb_EscortsDetails(FirstName,Surname, [CRB_Check], [CRB_Ref],ExpiryDate, Training, TrainingType)
    VALUES(@FirstName,@Surname,@CrbCheck,@CrbRef,@ExpiryDate,@Training,@TrainingType)
    set @Escort = SCOPE_IDENTITY()
    insert into tb_Addresses(EscortID,Address1,address2,Address3,City,PostCode,Email,Telephone,DateOfBirth,NationalINO)
    Values(@EscortID,@Address1,@Address2,@Address3,@City,@PostCode,@Email,@Telephone,@DateOfBirth,@NationalIN)
END

请确保将CREATE TABLE列替换为要使用的数据类型

希望这能有所帮助！