循环遍历 SQL 中的 SELECT 结果集

Looping through SELECT result set in SQL

提问by Reverend Bubbles

I'm not even sure how to word this question but here goes. I need to be able to loop through a result set, within the same SQL script, and use the results in more SQL.

我什至不知道如何表达这个问题，但在这里。我需要能够在同一个 SQL 脚本中循环遍历结果集，并在更多 SQL 中使用结果。

For example

例如

begin
SELECT (SELECT ColumnA, ColumnB from SomeTable) as x

loop through x(
    INSERT ColumnA into TableA
    INSERT ColumnB into TableB
    )
end

But I forget the exact way of doing this. I know I've done it before at a previous position, but I can't find the code for it in my files from that company.

但我忘记了这样做的确切方法。我知道我以前在以前的职位上做过这件事，但我在我的公司文件中找不到它的代码。

Obviously, this is a very crude and basic example and I plan on doing a lot more with the result set, but I just gave this as an example.

显然，这是一个非常粗略和基本的例子，我计划对结果集做更多的事情，但我只是把它作为一个例子。

---

---

EDIT: Here's a closer example of what I'm looking to do in case this will help.

编辑：这是我想要做的一个更接近的例子，以防万一这会有所帮助。

begin
    while(select columnA, columnB, columnC, columnD from myTable) as x

    begin
        INSERT columnA, columnB into TableA

        (get newly created ID of TableA - but that's a separate question involving @@IDENTITY)

        INSERT NewID, columnC, columnD into TableB
    end loop
end

采纳答案by GarethD

The usual way to handle obtaining the identity in a set based manner is through the OUTPUTclause:

以基于集合的方式处理获取身份的常用方法是通过OUTPUT子句：

INSERT INTO TableA (ColumnA, ColumnB)
OUTPUT inserted.Id, inserted.ColumnA, inserted.ColumnB
SELECT  ColumnA, ColumnB
FROM    MyTable;

The problem here is that what you would ideally like to do is this:

这里的问题是，您理想情况下想要做的是：

INSERT INTO TableA (ColumnA, ColumnB)
OUTPUT inserted.Id, MyTable.ColumnC, inserted.ColumnD 
    INTO TableB (AID, ColumnC, ColumnD)
SELECT  ColumnA, ColumnB
FROM    MyTable;

The problem is that you can't reference the source table in the OUTPUT, only the target. Fortunately there is a workaround for this using MERGE, since this allows you to use reference both the resident memory inserted table, and the source table in the output clause if you use MERGEon a condition that will never be true you can the output all the columns you need:

问题是你不能在 OUTPUT 中引用源表，只能引用目标。幸运的是，有一个解决方法 using MERGE，因为这允许您在输出子句中同时使用常驻内存插入表和源表的引用，如果您MERGE在永远不会为真的条件下使用，您可以输出所有列需要：

WITH x AS
(   SELECT  ColumnA, ColumnB, ColumnC, ColumnD
    FROM    MyTable
)
MERGE INTO TableA AS a
USING x
    ON 1 = 0 -- USE A CLAUSE THAT WILL NEVER BE TRUE
WHEN NOT MATCHED THEN 
    INSERT (ColumnA, ColumnB)
    VALUES (x.ColumnA, x.ColumnB)
OUTPUT inserted.ID, x.ColumnC, x.ColumnD INTO TableB (NewID, ColumnC, ColumnD);

The problem with this method is that SQL Server does not allow you to insert either side of a foreign key relationship, so if tableB.NewID references tableA.ID then the above will fail. To work around this you will need to output into a temporary table, then insert the temp table into TableB:

这种方法的问题在于 SQL Server 不允许您插入外键关系的任一侧，因此如果 tableB.NewID 引用 tableA.ID，则上述操作将失败。要解决此问题，您需要输出到临时表中，然后将临时表插入到 TableB 中：

CREATE TABLE #Temp (AID INT, ColumnC INT, ColumnD INT);
WITH x AS
(   SELECT  ColumnA, ColumnB, ColumnC, ColumnD
    FROM    MyTable
)
MERGE INTO TableA AS a
USING x
    ON 1 = 0 -- USE A CLAUSE THAT WILL NEVER BE TRUE
WHEN NOT MATCHED THEN 
    INSERT (ColumnA, ColumnB)
    VALUES (x.ColumnA, x.ColumnB)
OUTPUT inserted.ID, x.ColumnC, x.ColumnD INTO #Temp (AID, ColumnC, ColumnD);

INSERT TableB (AID, ColumnC, ColumnD)
SELECT AID, ColumnC, ColumnD
FROM #Temp;

Example on SQL Fiddle

SQL Fiddle 示例

回答by Giorgi Nakeuri

In SQLit is called CURSORS. The basic structure of CURSORis:

在SQL它被称为CURSORS. 的基本结构CURSOR是：

 DECLARE @ColumnA INT, @ColumnB INT

 DECLARE CurName CURSOR FAST_FORWARD READ_ONLY
 FOR
    SELECT  ColumnA, ColumnB
    FROM    SomeTable

 OPEN CurName

 FETCH NEXT FROM CurName INTO @ColumnA, @ColumnB

 WHILE @@FETCH_STATUS = 0
    BEGIN

        INSERT  INTO TableA( ColumnA )
        VALUES  ( @ColumnA )
        INSERT  INTO TableB( ColumnB )
        VALUES  ( @ColumnB )

        FETCH NEXT FROM CurName INTO @ColumnA, @ColumnB

    END

 CLOSE CurName
 DEALLOCATE CurName

Another way of iterative solution is WHILEloop. But for this to work you should have unique identity column in a table. For example

迭代解决方案的另一种方法是WHILE循环。但是要使其工作，您应该在表中具有唯一的标识列。例如

DECLARE @id INT

SELECT TOP 1 @id  =  id FROM dbo.Orders ORDER BY ID

WHILE @id IS NOT NULL
BEGIN

  PRINT @id

  SELECT TOP 1 @id  =  id FROM dbo.Orders WHERE ID > @id ORDER BY ID
  IF @@ROWCOUNT = 0
  BREAK

END

But note that you should avoid using CURSORSif there is alternative not iterative way of doing the same job. But of course there are a situations when you can not avoid CURSORs

但请注意，CURSORS如果有替代的非迭代方式来做同样的工作，你应该避免使用。但是当然也有无法避免的情况CURSORs

回答by user3514987

Don't use a cursor as it is not good when it comes to performance.

不要使用游标，因为它在性能方面不好。

Try out this link: http://support2.microsoft.com/kb/111401

试试这个链接：http: //support2.microsoft.com/kb/111401

You should use ROW_NUMBER() and populate the data into a temporay table -either variable or global table.

您应该使用 ROW_NUMBER() 并将数据填充到临时表中 - 无论是变量表还是全局表。

回答by Farhan

If you are only inserting data from one table to another, you can use this approach:

如果您只是将数据从一个表插入到另一个表，则可以使用这种方法：

insert into TableA (ColumnA, ColumnB, myTableID)
select (ColumnA, ColumnB, myTableID) from myTable

insert into TableB (ColumnC, ColumnD, myTableID)
select m.ColumnC, m.ColumnD, a.TableAid
from myTable m
join TableA a
    on m.myTableID = a.myTableID

Edit:

编辑：

You can add a column in TableAof the foreign key of myTable. This will help you get the ID of TableAin TableB.

您可以在TableA的外键中添加一列myTable。这将帮助您获得TableAin的 ID TableB。