• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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!
  • 2
  • 2
3 Solutions
Ephraim WangoyaCommented:

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
  set @I = 0
  while @I < @Counter
    insert into #NewTable 
    select @ID, @ItemID, @SDate
    set @I = @I + 1
  fetch cur into @ID, @ItemID, @SDate, @Counter

select * from #NewTable

close cur
deallocate cur

Open in new window

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'

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!
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
           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
DrevoAuthor Commented:
Thanks aqx and ewangoya! Your answers were all very helpful!
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.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now