从存储过程结果集中插入/更新表上的数据 [英] inserting/updating data on table from a stored procedure result set

问题描述

我有一个称为proc_item的存储过程，该过程从不同的表中获取数据(使用连接).我希望存储过程的结果集可以在另一个称为Item的表上插入(如果数据是新的)或更新(如果数据已经存在).有人可以给我一些有关如何执行此操作的想法吗?我是sql，存储过程和循环的新手.

I've a stored procedure called proc_item that fetches data from different tables (using join). I want the result set from the stored procedure to be either inserted (if the data is new) or updated (if the data already exists) on another table called Item. Can anybody give me some idea as to how i can do this? I'm new to sql, stored procedures and looping.

谢谢

推荐答案

看看@srutzky的解决方案，该解决方案更适合此问题

---

您可以做的第一件事是将所有内容写入表中. 为此，您需要定义一个表，该表具有与存储过程返回的列相同的列:

Take a look at @srutzky 's solution which is more appropriate to this problem

---

The first thing you could do is write everything into a table. To do so, you need to define a table, which has the same columns as they are returned by your stored procedure:

DECLARE @myTempTableName TABLE(
                                dataRow1 DATATYPE,
                                dataRow2 DATATYPE,
                                ...
                               )

INSERT INTO @myTempTableName(dataRow1, dataRow2,...)
EXEC( *mystoredprocedure* )

现在，您需要的所有数据都在表中.下一步是检查您需要更新什么以及要插入什么.假设datarow1是用于检查其是否已经存在的变量(例如:相同的名称或相同的ID) AND 并说它是唯一的(否则，您还需要女巫唯一的东西-迭代所需的东西)临时表)

Now all the data you need is in the table. Next step is to check what you need to update and what to insert. Let's say datarow1 is the variable to check if it already exists or not (for example: same name or same id) AND let's say it's unique (else you need also something witch is unique - needed for iterating through the temporary table)

DECLARE @rows INT,
        @dataRow1 DATATYPE,
        @dataRow2 DATATYPE, ...

-- COUNT Nr. of rows (how many rows are in the table)
SELECT 
    @rows = COUNT(1) 
FROM 
    @myTempTableName 

-- Loop while there are still some rows in the temporary table
WHILE (@rows > 0)

BEGIN

  -- select the first row and use dataRow1 as indicator which row it is. If dataRow1 is not unique the index should be provided by the stored procedure as an additional column
  SELECT TOP 1
    @dataRow1 = dataRow1,
    @dataRow2 = dataRow2, ..
  FROM
    @myTempTableName

  -- check if the value you'd like to insert already exists, if yes --> update, else --> insert
  IF EXISTS (SELECT * FROM *TableNameToInsertOrUpdateValues* WHERE dataRow1=@dataRow1)
      UPDATE 
            *TableNameToInsertOrUpdateValues*
      SET
            dataRow2=@dataRow2
      WHERE
            dataRow1=@dataRow1

  ELSE
      INSERT INTO 
            *TableNameToInsertOrUpdateValues* (dataRow1, dataRow2)
      VALUES 
            (@dataRow1, @dataRow2)


  --IMPORTANT: delete the line you just worked on from the temporary table
  DELETE FROM 
     @myTempTableName 
  WHERE 
     dataRow1= @dataRow1

  SELECT 
     @rows = COUNT(1) 
  FROM 
     @myTempTableName

END -- end of while-loop

可以在此查询开始时进行声明.我把它放在使用它的地方，以便于阅读.

The declaration can be done, at the beginning of this Query. I put it on the place where I used it so that it's easier to read.

我从中获得了代码的一部分，并且对遍历表有帮助(来自@cmsjr的解决方案，不带游标):

Where I got part of my Code from and also helpful for iterating through tables (solution from @cmsjr without cursor): Cursor inside cursor

这篇关于从存储过程结果集中插入/更新表上的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！