Блог

Главная - Вопросы по программированию - Вставить запрос для вставки нескольких строк в таблицу с помощью предложения select и output. SQL Server 2008

Вставить запрос для вставки нескольких строк в таблицу с помощью предложения select и output. SQL Server 2008

#sql #sql-server #transactions

#sql #sql-сервер #транзакции

Вопрос:

Я создал хранимую процедуру (пожалуйста, игнорируйте синтаксические ошибки)

 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 таблица имеет столбец identity ID с автоматически созданным ie. @outID1 и первый запрос insert вставляет только 1 строку.

configpthmaster таблица не связана напрямую с какой-либо другой таблицей и содержит 2 уникальные строки данных, которые я хочу извлечь для вставки данных в другие таблицы, одну за другой во время вставки.

Второй запрос insert извлекает данные из configpthmaster таблицы и вставляет 2 строки во configdetails время генерации (автоматически сгенерированного) идентификатора @outID2 , т.Е. .

Он также имеет FK, сопоставленный с server_details .

Проблема в том, что «@outID2» выдает только последний вставленный идентификатор (т.Е. если два идентификатора сгенерированы 100,101, я получаю 101), что в конечном итоге при 3-й вставке вставляет 2 строки только с тем же идентификатором 101, но я хочу, чтобы вставка была линейной. т.е. Одна для 100, а другая для 101.

Если при вставке были затронуты нулевые строки, как откатить транзакцию?

Как я могу выполнить эти требования? Пожалуйста, помогите.

Ответ №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

Комментарии:

1. Спасибо, но теперь, когда при 3-й вставке в configkeydetails в результате будет затронута строка 0: (я хочу, чтобы идентификатор, сгенерированный во 2-й вставке, использовался в 3-й, и соответствующее значение должно быть сохранено. @UnnikrishnanR

2. @ManojKargeti Я думаю, что это будет работать таким же образом. Он проверит, есть ли какие-либо записи в таблицах configpthmaster и configkeydetails с заданным appid , только после этого он начнет заполнять данные. для вставки в configkeydetails детали будут принимать вставленный configid из второй вставки.

3. Я выполнил предоставленный вами код, но в результате в 0 затронутых строках вставка в последние две таблицы выполняется один к одному, но с использованием «ПЕРЕКРЕСТНОГО ПРИМЕНЕНИЯ» для 2 записи в configpthmaster «ВЫБЕРИТЕ t.ID ,см.[ключ] ИЗ configpthmaster cm ПЕРЕКРЕСТНО ПРИМЕНИТЬ (ВЫБЕРИТЕ ИДЕНТИФИКАТОР ИЗ вывода, ГДЕ TableName=’configdetails’)t ГДЕ cm.appid = appid » дает 4 набора результатов @UnnikrishnanR

4. Будет две записи, если в configpthmaster есть два значения, соответствующие заданному @appid . Скажем, если config master повторно использует значения 2 и 3, соответствующие givan AppID, вторая вставка будет содержать две строки, соответствующие вставленным server_detials в первой таблице вставки server_detials.

5. Нет, пожалуйста, смотрите, ВЫБЕРИТЕ t.ID ,cm.[ключ] ИЗ configpthmaster cm ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ (ВЫБЕРИТЕ ИДЕНТИФИКАТОР ИЗ вывода, ГДЕ TableName=’configdetails’)t, ГДЕ cm.appid = appid ** допустим, ВЫБЕРИТЕ ИДЕНТИФИКАТОР ИЗ вывода, ГДЕ TableName=’configdetails’ дает 100,102, а configpthmaster имеет 2 строки сappid 143 и ключ x и appid 143 и ключ y ** ВЫБЕРИТЕ t.ID ,см.[ключ] даст 100 143 x и 100 143 y , 102 143 x и 102 143 y ???? @UnnikrishnanR

Ответ №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, ''), 'amp;amp;', 'amp;'), '')  ,cm.key, from configpthmaster cm where cm.appid = @appid )

begin 
commit
end

Я только что добавил МАТЕРИАЛ в ваш код.

Функция STUFF вставляет строку в другую строку.

Обратите внимание, что использование STUFF значительно замедляет время обработки кода.

для получения дополнительной информации о материалах