T-SQL Multi Insert Based on Source Record Value

Hi T-SQL Experts!

I am moving over data to a new database structure and need to insert the same record multiple times based on a particular value in that record. For example, I want to insert rows from a table with the following structure:

OldTable(MyID int, ItemID int, TrackedDate smalldatetime, Counter int)

Into this new structure:

NewTable(NewMyID, ItemID int, TracedDate smalldatetime)

I would like to repeat the INSERT Command for any rows in OldTable by as many times as specified in the Counter field value for that record.

Can anyone provide a clue as to how I can do this using t-sql and not creating a seperate application to do repeated INSERT command? Thanks!
DrevoAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:

You can try using a cursor
declare @I int, @ID int, @ItemID int, @SDate smalldatetime, @Counter int

declare cur cursor 
for select MyID, ItemID, TrackedDate, Counter 
from #OldTable

open cur

fetch cur into @ID, @ItemID, @SDate, @Counter

while @@FETCH_STATUS = 0
begin
  set @I = 0
  while @I < @Counter
  begin
    insert into #NewTable 
    select @ID, @ItemID, @SDate
    
    set @I = @I + 1
  end
  
  fetch cur into @ID, @ItemID, @SDate, @Counter
end

select * from #NewTable

close cur
deallocate cur

Open in new window

0
 
_agx_Connect With a Mentor Commented:
If it's just a 1-time insert, and the max counter doesn't exceed 2047, you could use the undocumented system table master.dbo.spt_values

INSERT INTO NewTable ( NewMyID, ItemID, TracedDate )
SELECT o.MyID, o.ItemID, o.TrackedDate
FROM   OldTable o INNER JOIN master.dbo.spt_values n
            ON n.Number BETWEEN 1 AND o.Counter
WHERE  n.type = 'P'


0
 
DrevoAuthor Commented:
Thanks agx and ewangoya!
The counter values are less than 2047, and this is a one time thing. Is either method preferred/faster? I'm processing about 2.5M records that split out (from the counter values) to about 12M.

Again, Thanks!
0
 
_agx_Connect With a Mentor Commented:
Usually a straight a single insert is faster than a cursor because cursors process 1 record at a time instead of as a batch.  But with such a large volume neither method will be instantaneous. However, I expect a cursor would be significantly slower.  But a lot depends on your environment.

I've cannot say I've used the master.dbo.spt_values with such a large table.  But whichever method you chose, I would recommend running some smaller tests first. Verify things work as expected.  Depending on your hardware you might consider breaking it up into smaller batches too.  For example, grab the max numeric ID in your table.  Then using a loop to INSERT only X records at a time.  X being a reasonable batch size for your environment.  

Anyway, a few pretests beforehand should give you a good idea what to expect.

....

WHILE @currID <= @MaxRecordID
BEGIN
           INSERT INTO NewTable ( NewMyID, ItemID, TracedDate )
           SELECT ...
           FROM   ....
           /*  only process X records at a time */
           WHERE  o.MyID BETWEEN @currID AND @currID + @batchSize
           AND       ....

           SET @currID = @currID + @batchSize   --- @batchSize == X records
END
0
 
DrevoAuthor Commented:
Thanks aqx and ewangoya! Your answers were all very helpful!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.