Solved

T-SQL Multi Insert Based on Source Record Value

Posted on 2011-09-13
5
280 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

732 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