I want to take an order table and do something like an unpivot into a new table. For each item row I want create a number of new rows equal to the quantity, but with the same data. I think the goal is best explained by the script below. This method does work, but I have 300 million rows to process and RBAR will take about 3 - 4 weeks to run.
The purpose for this change is to break out items so they can each have their own status. If a customer orders 20 of an item, 5 are picked up at purchase, 10 are scheduled for delivery and the last 5 are on backorder. I'd like to be able to update each item row rather than calculating quantity on the fly.
Is there a faster way to run this without using nested loops? CTE maybe?
create table dbo.tblOrder(ID int identity, quantity int, itemID int, shipDate datetime, backOrderFlag bit, someStatusCode char(3))
create table dbo.tblOrder2(ID int identity, quantity int, itemID int, shipDate datetime, backOrderFlag bit, someStatusCode char(3))
insert into dbo.tblOrder(quantity,itemID,shipdate, backorderflag, somestatusCode)
select 1,101,getdate(),0, 'ABC'
union
select 5,102,getdate(),0, 'ABC'
union
select 20,103,0,1, 'ABC'
select * from dbo.tblOrder
declare @ID int, @SQL varchar(MAX), @num int = 0
declare
@quantity int
,@itemID int
,@shipdate datetime
,@backorderflag bit
,@somestatusCode char(3)
select @ID = min(ID) from dbo.tblOrder
select
@quantity = quantity
,@itemID = itemID
,@shipdate = shipdate
,@backorderflag = backorderflag
,@somestatusCode = someStatusCode
from dbo.tblOrder
where ID = @ID
--select * from dbo.tblOrder
While(@ID is not null)
BEGIN
SET @SQL = 'INSERT INTO [dbo].[tblOrder2] ([quantity],[itemID],[shipDate],[backOrderFlag],[someStatusCode])'
+ 'select ''' + cast(@quantity as varchar) + ''',''' + cast(@itemID as varchar) + ''',''' + cast(@shipdate as varchar) + ''',''' + cast(@backorderflag as varchar) + ''',''' + @someStatusCode + ''''
While(@num < @quantity)
BEGIN
exec(@SQL)
SET @Num += 1
END
--select @ID as [id after inc]
select @ID = min(ID) from dbo.tblOrder where ID > @ID
select @quantity = quantity
,@itemID = itemID
,@shipdate = shipdate
,@backorderflag = backorderflag
,@somestatusCode = someStatusCode
from dbo.tblOrder
where ID = @ID
SET @num = 0
END
select * from tblOrder2
go
;with A(A) as (select 0 from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A))
select row_number() over (order by (select null)) ID,
o.quantity,
o.itemID,
o.shipDate,
o.backOrderFlag,
o.someStatusCode
from tblOrder o
cross apply(select top(o.quantity) '' x from a b, a c, a d, a e) T(C)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply