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:
- Create a virtual table, copying inside all the involved data from the source table(s)
- Loop over this virtual table, taking the ID (or/and other attributes) from one item
- Do the needed operation (in my sample there is another loop)
- Delete from the virtual table the used record
- 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! 😉