?
Solved

T-SQL Multi Insert Based on Source Record Value

Posted on 2011-09-13
5
Medium Priority
?
284 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 668 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 1332 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 1332 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

718 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