Looping through SELECT result set in SQL

Question

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.

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

Answer 1

In SQL it is called CURSORS. The basic structure of CURSOR is:

 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 WHILE loop. But for this to work you should have unique identity column in a table. For example

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 CURSORS if there is alternative not iterative way of doing the same job. But of course there are a situations when you can not avoid CURSORs

Answer 2

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

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 MERGE on a condition that will never be true you can the output all the columns you need:

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:

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