Solved

T-SQL Multi Insert Based on Source Record Value

Posted on 2011-09-13
5
282 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Drevo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 167 total points
ID: 36533259

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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 333 total points
ID: 36533998
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
 

Author Comment

by:Drevo
ID: 36551667
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 333 total points
ID: 36555091
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
 

Author Closing Comment

by:Drevo
ID: 36712969
Thanks aqx and ewangoya! Your answers were all very helpful!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question