I needed to dynamically generate missing items in database. I had a list of Sports and Categories, and I needed to generate items with all combinations of these two entities. But some of them already existed in database, so those should be skipped. My idea was to perform kind of for-each loop iterating over the Sports collection, but written in SQL. In each iteration I would check if given combination already exist in DB and if not, I create it.
SQL Cursor comes to help
If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one time process or nightly processing, this could do the trick
Here is the code (simplified for brevity)
declare @emailBody nvarchar(max) = 'Text of email body' declare @emailSubject nvarchar(max) = 'text of subject' declare @sportId uniqueIdentifier -- get all Sport Idies declare sportCursor cursor local static read_only forward_only for select Id from Sports where ObjectState = 1 -- iterate over Sport Idies open sportCursor fetch next from sportCursor into @sportId while @@fetch_status = 0 begin -- Check if entity with given parameters exist if not exists ( select * from EmailTemplates et where et.ObjectState = 1 and et.SportId = @sportId and et.Type = @NfSummaryType -- some other parameter, declaration omitted in this example ) Begin print 'creating new email template' Insert into EmailTemplates(Id, CreatedDate, Body, [Subject], SportId) values (NEWID(), GETDATE(), @emailBody, @emailSubject, @sportId) End fetch next from sportCursor into @sportId end CLOSE sportCursor DEALLOCATE sportCursor
If you want to know more about SQL Cursor then read here.