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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With