SQL 从存储过程结果集中插入/更新表上的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6197844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
inserting/updating data on table from a stored procedure result set
提问by aby
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.
我有一个名为 proc_item 的存储过程，它从不同的表中获取数据（使用连接）。我希望将存储过程的结果集插入（如果数据是新的）或更新（如果数据已经存在）到另一个名为 Item 的表上。任何人都可以给我一些关于如何做到这一点的想法吗？我是 sql、存储过程和循环的新手。
thanks
谢谢
采纳答案by skofgar
Take a look at @srutzky 's solution which is more appropriate to this problem
看看更适合这个问题的@srutzky 的解决方案
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* )
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) ANDlet's say it's unique (else you need also something witch is unique - needed for iterating through the temporary table)
现在您需要的所有数据都在表中。下一步是检查您需要更新的内容和插入的内容。假设 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
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.
可以在此查询的开头完成声明。我把它放在我使用它的地方，以便更容易阅读。
Where I got part of my Code from and also helpful for iterating through tables (solution from @cmsjr without cursor): Cursor inside cursor
我从哪里得到了我的部分代码，也有助于遍历表（来自@cmsjr 的解决方案，没有光标）：Cursor inside cursor
回答by Solomon Rutzky
You should create a Temporary Table to hold the results of the Stored Proc and then merge the results into your table. A Temporary Table is recommended over a Table Variable as it will JOIN better to the existing Table due to better statistics.
您应该创建一个临时表来保存存储过程的结果，然后将结果合并到您的表中。建议使用临时表而不是表变量，因为由于更好的统计，它会更好地加入现有表。
CREATE TABLE #TempResults
(
Field1 DATATYPE1,
Field2 DATATYPE2,
...,
PRIMARY KEY CLUSTERED (KeyField1,...)
)
INSERT INTO #TempResults (Field1, Field2, ...)
EXEC Schema.ProcName @Param1, ...
Now, there are two ways to do the merge. The first works in all versions of SQL Server and the second uses a command that was introduced in SQL Server 2008.
现在，有两种方法可以进行合并。第一个适用于所有版本的 SQL Server，第二个使用 SQL Server 2008 中引入的命令。
-- this should work on all SQL SERVER versions
UPDATE rt
SET rt.Field2 = tmp.Field2,
...
FROM Schema.RealTable rt
INNER JOIN #TempResults tmp
ON tmp.KeyField1 = rt.KeyField1
...
INSERT INTO Schema.RealTable (Field1, Field2, ...)
SELECT tmp.Field1, tmp.Field2, ...
FROM #TempResults tmp
LEFT JOIN Schema.RealTable rt
ON rt.KeyField1 = tmp.KeyField1
...
WHERE rt.KeyField1 IS NULL
OR:
或者：
-- the MERGE command was introduced in SQL SERVER 2008
MERGE Schema.RealTable AS target
USING (SELECT Field1, Field2,... FROM #TempResults) AS source (Field1, Field2,..)
ON (target.KeyField1 = source.KeyField1)
WHEN MATCHED THEN
UPDATE SET Field2 = source.Field2,
...
WHEN NOT MATCHED THEN
INSERT (Field1, Field2,...)
SELECT tmp.Field1, tmp.Field2, ...
FROM #TempResults tmp
For more information on the MERGE command, go here:
http://msdn.microsoft.com/en-us/library/bb510625(v=SQL.100).aspx
有关 MERGE 命令的更多信息，请访问：http: //msdn.microsoft.com/en-us/library/bb510625(v=SQL.100)
.aspx
Now, if you have a large result set to merge and the table you are merging into is very large and has a lot of activity on it where this type of operation might cause some blocking, then it can be looped to do sets of 1000 rows at a time or something like that. Something along the lines of this:
现在，如果您有一个大的结果集要合并，并且您要合并的表非常大并且上面有很多活动，这种类型的操作可能会导致一些阻塞，那么可以循环它以执行 1000 行的集合一次或类似的事情。类似的东西：
<insert CREATE TABLE / INSERT...EXEC block>
CREATE TABLE #CurrentBatch
(
Field1 DATATYPE1,
Field2 DATATYPE2,
...
)
DECLARE @BatchSize SMALLINT = ????
WHILE (1 = 1)
BEGIN
-- grab a set to work on
DELETE TOP (@BatchSize)
OUTPUT deleted.Field1, deleted.Field2, ...
INTO #CurrentBatch (Field1, Field2, ...)
FROM #TempResults
IF (@@ROWCOUNT = 0)
BEGIN
-- no more rows
BREAK
END
<insert either UPDATE / INSERT...SELECT block or MERGE block from above
AND change references to #TempResults to be #CurrentBatch>
TRUNCATE TABLE #CurrentBatch
END
回答by Andriy M
You need first to insert the data into a temporary container, like a temporary table or a table variable. Then you can work with the table as usual: join it, derive result sets from it etc.
您首先需要将数据插入到临时容器中，例如临时表或表变量。然后你可以像往常一样处理表：加入它，从中导出结果集等。
Check this questionfor the options for storing the output of an SP into a table.
检查此问题以了解将 SP 的输出存储到表中的选项。
