When you have to do some data manipulation in a relational database and you want to use only SQL scripts, looping on your data can be very useful. This is possible only on some DBMS (for example, not for MS Access). Now we are going to see how to do it with SQL Server.

This operation has been very important for me, when I had to migrate an old version of a working database in a newer one and, for various reasons, I used only SQL script code.

For example, if you have two table, let’s say Table1 and Table2, and you want to populate with their data Table3 [ID, IDTable1, IDTable2, other attributes..], how do you proceed? Let’s look the code!


select ID
into #ControlTable1
from [dbo].[TabGruppiVeicoli]

declare @Table1ID int
declare @Table2ID int

while exists (select * from #ControlTable1)
begin
select top 1 @Table1ID = ID
from #ControlTable1
order by ID asc
IF OBJECT_ID('tempdb..#ControlTable2') IS NOT NULL DROP TABLE #ControlTable2
select ID
into #ControlTable2
from [dbo].[TabGruppiAutisti]
while exists (select * from #ControlTable2)
begin
select top 1 @Table2ID = ID
from #ControlTable2
order by ID asc
insert into Table3 (IDTable1, IDTable2) VALUES (@Table1ID, @Table2ID);

delete #ControlTable2
where ID = @Table2ID
end
delete #ControlTable1
where ID = @Table1ID

end
BEGIN TRY
drop table #ControlTable1
END TRY
BEGIN CATCH
END CATCH

The idea is simple:

  1. Create a virtual table, copying inside all the involved data from the source table(s)
  2. Loop over this virtual table, taking the ID (or/and other attributes) from one item
  3. Do the needed operation (in my sample there is another loop)
  4. Delete from the virtual table the used record
  5. At the end of the loop, remove the virtual table

I have used and tested this script on SQL Server 2008, 2012 and 2014. It works perfectly! 😉

Advertisements