Insert查询通过select和output子句在表中插入多行.SQL Server 2008

Question

我已经创建了一个存储过程(请忽略语法错误)

alter proc usp_newServerDetails
   (@appid int, @envid int, @serType varchar(20), @servName varchar(20))
as
    declare @oTbl_sd table (ID int)
    declare @outID1
    declare @oTbl_cd table (ID int)
    declare @outID2
    begin Transaction
        insert into server_details(envid, servertype, servername)
        output inserted.serverid into @oTbl_sd(ID)
        values(@envid, @serType, @servName)
        select @outID1 = ID from @oTbl_sd
        insert into configdetails(serverid, servertype, configpath, configtype)
        output inserted.configid into @oTbl_cd(ID)
        (select @outID1, cm.servertype, cm.configpath, cm.configtype 
         from configpthmaster cm 
         where cm.appid = @appid )
        select @outID2 = ID from @oTbl_cd
        insert into configkeydetails(confiid, keyname)
        output inserted.Keyid into @oTbl_ckd(ID)
        (select @outID2, cm.key 
         from configpthmaster cm 
         where cm.appid = @appid)
    begin 
    commit
    end

server_details表有一个自动生成的标识列ID。@outID1和first insert查询只插入1行

configpthmaster表与任何其他表直接无关，并且有2个唯一的数据行，我想在插入期间逐个获取以将数据插入到其他表中。

第二个insert查询从configpthmaster表中获取数据并在configdetails中插入2行，同时生成(自动生成)ID ie。@outID2 .

它也有一个FK映射到server_details。

问题是"@outID2"给出最后插入的ID仅(即。如果两个id生成100,101，我得到101)，最终在第三次插入，插入2行相同的id 101，但我希望插入应该是线性的。例如，一个100，另一个101。

如果插入时不影响行，如何回滚事务?

我怎样才能达到这些要求?请帮助。

Answer 1

按如下方式更改程序，然后再试一次。

ALTER PROCEDURE usp_newServerDetails(@appid int, @envid int,@serType varchar(20),@servName varchar(20))
    AS
BEGIN
  BEGIN TRY
   DECLARE @Output TABLE (ID int,TableName VARCHAR(50),cmKey VARCHAR(50)) --table variable for keeping Inserted ID's
        BEGIN TRAN

         IF EXISTS ( SELECT 1 FROM configpthmaster cm  WHERE cm.appid = @appid ) 
                        AND  ( SELECT 1 FROM configkeydetails ck  WHERE ck.appid = @appid ) --add a conditon to satisfy the valid insertions
         BEGIN
            INSERT INTO server_detials(envid,servertype,servername)
                OUTPUT inserted.serverid,'server_detials',NULL INTO @Output(ID,TableName,cmKey )
            VALUES(@envid ,@serType ,@servName)
            INSERT INTO configdetails(serverid,servertype,configpath,configtype)
                OUTPUT inserted.configid,'configdetails',cm.Key INTO @Output(ID,TableName,cmKey )
            SELECT t.ID,cm.servertype,cm.configpath,cm.configtype 
            FROM configpthmaster cm 
              CROSS APPLY (SELECT ID FROM @Output WHERE TableName='server_detials')t
            WHERE cm.appid = @appid
            INSERT INTO configkeydetails(configId,keyname)
            SELECT ID,cmKey FROM @Output 
            WHERE TableName='configdetails'
        END
       COMMIT TRAN
  END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 
        ROLLBACK 
    END CATCH
END

Answer 2

你能试试这个解决方案吗?

alter proc usp_newServerDetails(@appid int, @envid int,@serType varchar(20),@servName varchar(20))
as
declare @oTbl_sd table (ID int)
declare @outID1
declare @oTbl_cd table (ID int)
declare @outID2
begin Transaction
insert into server_detials(envid,servertype,servername)
output inserted.serverid into @oTbl_sd(ID)
values(@envid ,@serType ,@servName)
select @outID1 = ID from @oTbl_sd
insert into configdetails(serverid,servertype,configpath,configtype)
output inserted.configid into @oTbl_cd(ID)
(select @outID1 ,cm.servertype,cm.configpath,cm.configtype from configpthmaster cm where cm.appid = @appid )
select @outID2 = ID from @oTbl_cd
insert into configkeydetails(confiid,keyname)
output inserted.Keyid into @oTbl_ckd(ID)
(select  isnull(replace(stuff((SELECT inserted.configid FOR xml path('')), 1, 1, ''), '&', '&'), '')  ,cm.key, from configpthmaster cm where cm.appid = @appid )
begin 
commit
end

我刚刚在你的代码中添加了一些东西。

STUFF函数将一个字符串插入到另一个字符串中。

请注意，使用STUFF会大大降低代码的处理时间。

查看更多关于STUFF的信息