The topic is quite old, but I found it really helpful, so be warned.
Scenario: Iterate over a result set and insert it in a new table in T-SQL
I had to write a SQL migration script to move date from an old table into a new table with a new primary key.
Update! I discovered that my problem would have been solved with a much simpler SQL script (INSERT INTO x …(SELECT … FROM Y)). So my example here is pretty dumb - sorry if this confuses you, but I will keep the blogpost to show the mechanics. Thanks Mark!
Here was/is my resulting script using T-SQL Cursors:
DECLARE @TemplateId as uniqueidentifier; DECLARE @UserId as uniqueidentifier; DECLARE @OldTemplateFavCursor as CURSOR; SET @OldTemplateFavCursor = CURSOR FOR SELECT UserTemplate.[Template_Id], UserTemplate.[User_Id] FROM UserTemplate; OPEN @OldTemplateFavCursor; FETCH NEXT FROM @OldTemplateFavCursor INTO @TemplateId, @UserId; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.[UserFavoriteTemplate] ([Id] ,[TemplateId] ,[UserId]) VALUES (NEWID() ,@TemplateId ,@UserId) FETCH NEXT FROM @OldTemplateFavCursor INTO @TemplateId, @UserId; END CLOSE @OldTemplateFavCursor; DEALLOCATE @OldTemplateFavCursor;
In the first couple of lines we just declare some variables.
In this particular script we want to move the “TemplateId” & “UserId” from the table “UserTemplate” into the target table “UserFavoriteTemplate”, but I also want to store an additional GUID as Id.
This line will select our current data into the cursor:
SET @OldTemplateFavCursor = CURSOR FOR SELECT UserTemplate.[Template_Id], UserTemplate.[User_Id] FROM UserTemplate;
With the “OPEN”, “FETCH NEXT” and “CLOSE” we move the cursor and inside the “WHILE” we can do our migration.
The syntax seems (from a C# perspective) strange, but works well for this scenario.
I wouldn’t recommend this approach for large scale migrations or actual production code because I heard that the performance is not as great as some clever joins or other T-SQL magic.
Make sure you really need this
You can do some clever joins with SQL - make sure you really need this approach. My example here is not a clever one, so use this feature wisely. (again - thanks to Mark for the comment!)
Thanks Christopher for your help!