如何通过单个存储过程在多个表中插入值 [英] how to insert values in multiple table through single store procedure

问题描述

我通过一个商店程序在不同的表中插入值。在插入一个表（A）后，我需要下一个表（B）中的主键值作为外键下一个表（B）我在sql server management studio 2012中工作。请帮我解决这个问题。

 ALTER PROCEDURE [dbo]。[usp_InsertCustomer_Subscription] 
  - 添加参数< span class =code-keyword> for 此处的存储过程
 @FK_UserId nvarchar（ 128 ），
 @FK_AdvertiserDetailsId < span class =code-keyword> int ，
 @FK_CityId  int ，
 @FK_CategoryId  int ，
 @FK_AdvertiserId  int ，
 @AddedOn datetime，
 @AddedBy nvarchar（ 128 ），
 @UpdatedOn datetime，
 @UpdatedBy nvarchar（ 128 ），
 @IsActive位，
 @SubscriptionExpiryDate datetime 
 
 AS 
 BEGIN 
 DECLARE @CustomerSubId  int  $ b添加$ b  -  SET NOCOUNT ON以防止额外的结果集来自 
  - 干扰SELECT状态发言：。 
 SET NOCOUNT ON; 
 SET IDENTITY_INSERT Customer_Subscription_Accounts ON; 
  - 在此处插入语句 过程
将插入 Customer_Subscription（FK_UserId，FK_AdvertiserDetailsId） ，FK_CityId，FK_CategoryId，FK_AdvertiserId，AddedOn，AddedBy，UpdatedOn，UpdatedBy，IsActive，SubscriptionExpiryDate）值（@ FK_UserId，@ FK_AdvertiserDetailsId，@ FK_CityId，@ FK_CategoryId，@ FK_AdvertiserId，@ AddedOn，@ AddedBy，@ UpdatedOn，@ UpdatedBy，@ IsActive ，@ SubscriptionExpiryDate）
选择@CustomerSubId = @@ IDENTITY 
 
 
将插入 Customer_Subscription_Accounts（SubscriptionDate，FK_Customer_SubscriptionId，AddedOn ，AddedBy，UpdatedOn，UpdatedBy，IsActive，SubscriptionExpiryDate）值（GETDATE（），@ CustomerSubId，@ AddedOn，@ AddedBy，@ UpdatedOn，@ UpdatedBy，@ IsActive，@ SubscriptionExpiryDate）
 
 SET IDENTITY_INSERT Customer_Subscription_Accounts OFF ; 
结束

当我使用此商店程序时通过错误消息

Quote：

无法将值NULL插入列'Id'，表'AirAdsWellDone.dbo .Customer_Subscription';列不允许空值。 INSERT失败。

无法将值NULL插入ReceivedBy列，表'AirAdsWellDone.dbo.Customer_Subscription_Accounts';列不允许空值。 INSERT失败。

语句已被终止。

语句已被终止。

解决方案

< blockquote>看起来你很亲密。您只需删除两个 SET IDENTITY_INSERT 行，并将 @@ IDENTITY 替换为 SCOPE_IDENTITY ：

  ALTER  程序 [dbo]。[usp_InsertCustomer_Subscription] 
  @ FK_UserId   nvarchar （ 128 ），
  @ FK_AdvertiserDetailsId   int ， 
  @ FK_CityId   int ，
  @FK_CategoryId   int ，
  @ FK_AdvertiserId   int ，
  @ AddedOn   datetime ，
  @ AddedBy   nvarchar （ 128 ），
  @ UpdatedOn   datetime ，
  @ UpdatedBy   nvarchar （ 128 ），
  @ IsActive  位，
  @ SubscriptionExpiryDate   datetime  
  AS  
  BEGIN  
  DECLARE   @ CustomerSubId   int ; 
 
  SET   NOCOUNT   ON < /跨度>; 
 
  INSERT   INTO  Customer_Subscription 
（
 FK_UserId， 
 FK_AdvertiserDetailsId，
 FK_CityId，
 FK_CategoryId，
 FK_AdvertiserId，
 AddedOn，
 AddedBy，
 UpdatedOn，
 UpdatedBy，
 IsActive，
 SubscriptionExpiryDate 
）
  VALUES  
（
  @FK_UserId ，
  @ FK_AdvertiserDetailsId ，
  @ FK_CityId  ，
  @ FK_CategoryId ，
  @ FK_AdvertiserId ，
  @ AddedOn ，
  @ AddedBy ，
  @ Updat edOn ，
  @ UpdatedBy ，
  @ IsActive ，
  @ SubscriptionExpiryDate  
）; 
 
  SET   @ CustomerSubId  =  SCOPE_IDENTITY （）; 
 
  INSERT   INTO  Customer_Subscription_Accounts 
（
 SubscriptionDate， 
 FK_Customer_SubscriptionId，
 AddedOn，
 AddedBy，
 UpdatedOn，
 UpdatedBy，
 IsActive，
 SubscriptionExpiryDate 
）
  VALUES  
（
 GETDATE（），
  @ CustomerSubId ，
  @ AddedOn ，
  @ AddedBy ，
  @ UpdatedOn ，
  @ UpdatedBy ，
  @IsActive ，
  @ SubscriptionExpiryDate  
）; 
  END  

SQL SERVER - @@ IDENTITY vs SCOPE_IDENTITY（）vs IDENT_CURRENT - 检索上次插入的标识记录 [ ^ ]

i am insert the values in different tables through one store procedure. after insertion of one table (A) i need the primary key values in next one table (B) as a foreign key next table(B) I am working in sql server management studio 2012 . please help me on this scenarios.

ALTER PROCEDURE [dbo].[usp_InsertCustomer_Subscription]
	-- Add the parameters for the stored procedure here
	@FK_UserId nvarchar(128),
	@FK_AdvertiserDetailsId int,
	@FK_CityId int,
	@FK_CategoryId int,
	@FK_AdvertiserId int,
	@AddedOn datetime,
	@AddedBy nvarchar(128),
	@UpdatedOn datetime,
	@UpdatedBy nvarchar(128),
	@IsActive bit,
	@SubscriptionExpiryDate datetime
	
AS
BEGIN
DECLARE @CustomerSubId int
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET IDENTITY_INSERT Customer_Subscription_Accounts ON;
    -- Insert statements for procedure here
	insert into Customer_Subscription(FK_UserId,FK_AdvertiserDetailsId,FK_CityId,FK_CategoryId,FK_AdvertiserId,AddedOn,AddedBy,UpdatedOn,UpdatedBy,IsActive,SubscriptionExpiryDate)values(@FK_UserId,@FK_AdvertiserDetailsId,@FK_CityId,@FK_CategoryId,@FK_AdvertiserId,@AddedOn,@AddedBy,@UpdatedOn,@UpdatedBy,@IsActive,@SubscriptionExpiryDate)
	Select @CustomerSubId=@@IDENTITY


	insert into Customer_Subscription_Accounts(SubscriptionDate,FK_Customer_SubscriptionId,AddedOn,AddedBy,UpdatedOn,UpdatedBy,IsActive,SubscriptionExpiryDate) values(GETDATE(),@CustomerSubId,@AddedOn,@AddedBy,@UpdatedOn,@UpdatedBy,@IsActive,@SubscriptionExpiryDate)

	SET IDENTITY_INSERT Customer_Subscription_Accounts OFF;
END

when i use this store procedure that through error message

Quote:

Cannot insert the value NULL into column 'Id', table 'AirAdsWellDone.dbo.Customer_Subscription'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'ReceivedBy', table 'AirAdsWellDone.dbo.Customer_Subscription_Accounts'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The statement has been terminated.

解决方案

Looks like you're close. You just need to remove the two SET IDENTITY_INSERT lines, and replace @@IDENTITY with SCOPE_IDENTITY:

ALTER PROCEDURE [dbo].[usp_InsertCustomer_Subscription]
    @FK_UserId nvarchar(128),
    @FK_AdvertiserDetailsId int,
    @FK_CityId int,
    @FK_CategoryId int,
    @FK_AdvertiserId int,
    @AddedOn datetime,
    @AddedBy nvarchar(128),
    @UpdatedOn datetime,
    @UpdatedBy nvarchar(128),
    @IsActive bit,
    @SubscriptionExpiryDate datetime
AS
BEGIN
DECLARE @CustomerSubId int;
    
    SET NOCOUNT ON;
    
    INSERT INTO Customer_Subscription
    (
        FK_UserId,
        FK_AdvertiserDetailsId,
        FK_CityId,
        FK_CategoryId,
        FK_AdvertiserId,
        AddedOn,
        AddedBy,
        UpdatedOn,
        UpdatedBy,
        IsActive,
        SubscriptionExpiryDate
    )
    VALUES
    (
        @FK_UserId,
        @FK_AdvertiserDetailsId,
        @FK_CityId,
        @FK_CategoryId,
        @FK_AdvertiserId,
        @AddedOn,
        @AddedBy,
        @UpdatedOn,
        @UpdatedBy,
        @IsActive,
        @SubscriptionExpiryDate
    );
    
    SET @CustomerSubId = SCOPE_IDENTITY();
    
    INSERT INTO Customer_Subscription_Accounts
    (
        SubscriptionDate,
        FK_Customer_SubscriptionId,
        AddedOn,
        AddedBy,
        UpdatedOn,
        UpdatedBy,
        IsActive,
        SubscriptionExpiryDate
    )
    VALUES
    (
        GETDATE(),
        @CustomerSubId,
        @AddedOn,
        @AddedBy,
        @UpdatedOn,
        @UpdatedBy,
        @IsActive,
        @SubscriptionExpiryDate
    );
END

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record[^]

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