您可以做的第一件事是将所有内容写入表中。为此,您需要定义一个表,该表具有与存储过程返回的列相同的列:
DECLARE @myTempTableName TABLE(
daTarow1 DATATYPE,
daTarow2 DATATYPE,
...
)
INSERT INTO @myTempTableName(daTarow1, daTarow2,...)
EXEC( *mystoredprocedure* )
现在,您需要的所有数据都在表中。下一步是检查您需要更新什么以及要插入什么。假设daTarow1是用于检查其是否已经存在的变量(例如:相同的名称或相同的ID) 并且说它是唯一的(否则,您还需要某些女巫是唯一的-遍历临时表时需要)
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
声明可以在此查询开始时完成。我将其放在使用它的地方,以便于阅读。