SQL 如何在没有 pk 的存储过程中遍历临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13012200/
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
How do I loop through a temp table in a stored procedure with no pk
提问by Steve's a D
Problem:
问题：
I need to loop through the records in one table, pulling the employee number and comparing this employee number against another table to see if they're still active employees. If they are no longer active employees I need to pass the data from this row into another stored proc.
我需要遍历一个表中的记录，提取员工编号并将该员工编号与另一张表进行比较，以查看他们是否仍然是活跃员工。如果他们不再是活跃员工，我需要将数据从这一行传递到另一个存储过程。
Research:
研究：
I've googled around quite a bit and realize that I shouldn't use cursors for this. I did however, find the following examples:
我已经搜索了很多，并意识到我不应该为此使用游标。但是，我确实找到了以下示例：
- http://ask.sqlservercentral.com/questions/7969/loop-through-records-in-a-temporary-table.html
- http://eedle.com/2010/11/11/looping-through-records-in-sql-server-stored-procedure/
- http://ask.sqlservercentral.com/questions/7969/loop-through-records-in-a-temporary-table.html
- http://eedle.com/2010/11/11/looping-through-records-in-sql-server-stored-procedure/
However, it seems like they use a pk to loop through the records. Employee numbers can be the same for multiple recods in my scenario
但是，似乎他们使用 pk 来循环记录。在我的场景中，多个记录的员工编号可以相同
Questions:
问题：
- Is it possible to achieve what I'm attempting without cursors?
- If it is possible, how would I go about fetching each row with a non unique column?
- 是否可以在没有游标的情况下实现我正在尝试的功能？
- 如果可能，我将如何使用非唯一列获取每一行？
回答by RBarryYoung
Since you haven't given us a full description of your situation, we cannot give a complete answer, however, in general, it's Loopsthat you want to avoid in a set-based language like SQL and not Cursors per se(the problem with Cursosr is that they requirethe Loops).
由于您没有向我们完整描述您的情况，我们无法给出完整的答案，但是，总的来说，您希望在基于集合的语言（如 SQL）中避免使用循环而不是游标本身（问题在于Cursosr 是他们需要循环）。
In your comments you provide a little bit more information in that you want to "loop through first table, compare to second table, and when the compare fails i delete records from first table. In essense I'm deleting recods from the first table of employees who are no longer with the company."
在您的评论中，您提供了更多信息，因为您希望“循环遍历第一个表，与第二个表进行比较，当比较失败时，我从第一个表中删除记录。本质上，我正在从第一个表中删除记录不再在公司工作的员工。”
Here is how you can do this in SQL:
以下是在 SQL 中执行此操作的方法：
DELETE From FirstTable
WHERE FirstTable.EmployeeID NOT IN
(
SELECT SecondTable.EmployeeID
FROM SecondTable
WHERE SecondTable.Flag = 'Y'
)
No Loops are needed ...
不需要循环...
If the issue then is that you want to use a pre-existing Stored Procedure to do the deletion, then there are a several possibilities:
如果问题是您想使用预先存在的存储过程来进行删除，那么有几种可能性：
First, you can extract the contents of the Stored Procedure and re-write them for these preceding WHERE conditions. I understand that this is code duplication and that it violates some people's DRY instincts, however, understand that SQL is NOTan Object-Oriented development environment and that sometimes code duplication has to happen.
首先，您可以提取存储过程的内容并针对前面的 WHERE 条件重写它们。我知道这是代码重复，它违反了某些人的 DRY 直觉，但是，请理解 SQL不是面向对象的开发环境，有时必须发生代码重复。
The second option would be to refactor the stored procedure so that it could accept a TableParameter for its EmployeeId's to Delete. This is complicated though, and we would need to see that stored procedure to advise you on it.
第二个选项是重构存储过程，以便它可以接受要删除的 EmployeeId 的 TableParameter。不过，这很复杂，我们需要查看该存储过程来为您提供建议。
The third option would be to use string aggregation to build dynamic SQL to call the stored procedure for each EmployeeID to be deleted like so:
第三种选择是使用字符串聚合来构建动态 SQL 来调用每个要删除的 EmployeeID 的存储过程，如下所示：
DECLARE @sql As NVarchar(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
EXEC YourProc ''' + CAST(EmployeeID As NVARCHAR(MAX)) + '''; '
FROM FirstTable
WHERE FirstTable.EmployeeID IN
(
SELECT SecondTable.EmployeeID
FROM SecondTable
WHERE SecondTable.Flag = 'Y'
)
EXEC(@sql);
This avoids both the Looping and the Cusror problems, though many dislike it also. I prefer this solution myself, largely because of its generality.
这避免了循环和 Cusror 问题，尽管许多人也不喜欢它。我自己更喜欢这个解决方案，主要是因为它的通用性。
回答by Tobsey
This will delete all records from your employee data table if there are no matching rows in your current employees table.
如果当前员工表中没有匹配的行，这将从员工数据表中删除所有记录。
I'd sugest you replace the DELETE FROMwith SELECT * FROMand then when you're happy to delete the results change it back to DELETE
我建议你替换为DELETE FROM，SELECT * FROM然后当你很高兴删除结果时，将它改回DELETE
DELETE FROM
EmployeeDataTable
WHERE
NOT EXISTS
(SELECT
NULL
FROM
CurrentEmployees
WHERE
EmployeeDataTable.EmployeeID = CurrentEmployees.EmployeeID
)
EDIT: Just saw your comment about the Active flag, this means the query can be changed to
编辑：刚刚看到您对 Active 标志的评论，这意味着查询可以更改为
DELETE FROM
EmployeeDataTable
WHERE
EXISTS
(SELECT
NULL
FROM
CurrentEmployees
WHERE
EmployeeDataTable.EmployeeID = CurrentEmployees.EmployeeID
CurrentEmployees.IsActive <> 'Y'
)
回答by Jerry
I would do this by looping through a cursor. You can also add a unique ID to the cursor so you know which row you are currently on.
我会通过循环游标来做到这一点。您还可以向游标添加唯一 ID，以便了解当前所在的行。
DECLARE @id uniqueidentifier
DECLARE @empName VARCHAR(50)
SELECT newId() AS Id, *
INTO #mytemp
FROM MyEmployees
ORDER BY EmpName
while EXISTS(SELECT TOP 1 1 FROM #mytemp)
BEGIN
--Get row from cursor to process
SELECT TOP 1 @id = Id, @empName = EmpName FROM #mytemp
--Do you processing here. Call other stored proc.
--Remove processed row from cursor.
DELETE FROM #mytemp WHERE Id = @id
END
DROP TABLE #mytemp
回答by Dominic Goulet
Have you considered using the MERGEstatement : http://technet.microsoft.com/en-us/library/bb510625.aspx
您是否考虑过使用该MERGE声明：http: //technet.microsoft.com/en-us/library/bb510625.aspx
You have clauses for :
您有以下条款：
- When the data does match between both table
- When the data exists in source, but not in target
- When the data exists in target, but not in source
- 当两个表之间的数据匹配时
- 当数据存在于源中但不存在于目标中时
- 当数据存在于目标中，但不存在于源中时
You can then insert, update or delete records depending on the match type. You can also output the action from match to a temporary table for additional custom operations.
然后您可以根据匹配类型插入、更新或删除记录。您还可以将 match 的操作输出到临时表以进行其他自定义操作。
For instance, you can do :
例如，你可以这样做：
MERGE INTO Table1
USING Table2 ON Table1.EmployeeID = Table2.EmployeeID
WHEN MATCHED
THEN UPDATE SET Table1.SomeField = Table2.SomeOtherField
WHEN NOT MATCHED BY SOURCE
THEN DELETE
WHEN NOT MATCHED BY TARGET
THEN Insert (Name, Status) VALUES (EmployeeName, 'Active')
回答by UnhandledExcepSean
- It would be possible if you took the content from the sproc you are wanting to call inside your cursor and implemented that logic within your current sproc. At this point, you should be able to use set-based logic. Is the sproc you are calling very complex?
- What about adding an identity column to the temp table?
- 如果您从要在游标内调用的 sproc 中获取内容并在当前 sproc 中实现该逻辑，那将是可能的。此时，您应该能够使用基于集合的逻辑。您调用的 sproc 是否非常复杂？
- 将标识列添加到临时表怎么样？
Sometimes, cursors are the right solution for a variety of reasons.
有时，出于各种原因，游标是正确的解决方案。
