January 17, 2012 at 4:47 pm
Hi,
I have two tables
OrderHeader
OrdersMissing
On the OrdersMissing table it will have the following information:
KeyNo Qty
1234 3
1235 23
1783 5
This tells me how many lines I need to re insert back into the OrderHeader Table
so I need to create some kind of cursor that will loop round and insert into the Order Header Table untill it has inserted the correct amount, but unsure how to do this? Any ideas?
example:-
insert into OrderHeader values() From OrdersMissing where KeyNo 1234
insert into OrderHeader values() From OrdersMissing where KeyNo 1234
insert into OrderHeader values() From OrdersMissing where KeyNo 1234
Thanks
January 17, 2012 at 5:13 pm
I had a similar requirement once upon a time.
Here is how I solved it. You will need to obviously adapt the structures to your needs, but the principles should help.
http://jasonbrimhall.info/2010/12/13/sql-confessions/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 6:36 pm
Hi,
This may not be the best way, but you can do something like this :
--Populate Test Data
declare @OrderMissing as table(
KeyNoteint,
Qtyint
)
insert into @OrderMissing (KeyNote, Qty)
select 1234,3 union
select 1235,23 union
select 1783,5
declare @OrderHeaderMissing as table(
RowIdint,
KeyNoteint
)
--Helper Table
declare @Helper as table(
MaxQty int
)
declare
@max-2 as int
select @max-2 = max(Qty) from @OrderMissing
;with cte_base as (
select 1 as Num union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 0
)
insert into @Helper (MaxQty)
select
one.Num + (ten.Num * 10) as MaxQty
from cte_base as one
cross join cte_base as ten
where
one.Num + (ten.Num * 10) <= @max-2
and one.Num + (ten.Num * 10) > 0
/*
** Please note that I am adding upto 23 since the sample data you have provided had the 23 as max. But if you have more than that in the
production environment make sure to add hundreds, thousands also.
e.g :
select one.Num + (ten.Num * 10) + (ten.Num * 10) + (hundred.Num * 100)
from cte_base as one
cross join cte_base as ten
cross join cte_base as hundred
<where_condition>
*/
-------------------------------------------------------------
;with cte_missing as (
select
row_number() over(order by A.KeyNote) as RowId,
row_number() over(partition by A.KeyNote order by A.KeyNote) as SubId,
A.KeyNote
from @OrderMissing as A
join @Helper as H on H.MaxQty <= A.Qty
)
insert into @OrderHeaderMissing (RowId, KeyNote)
select M.RowId,M.KeyNote
from cte_missing as M
select * from @OrderHeaderMissing
Hope this will solve your porblem. 🙂
--------
Manjuke
http://www.manjuke.com
January 17, 2012 at 7:26 pm
it's all right.
manjuke (1/17/2012)
Hi,This may not be the best way, but you can do something like this :
--Populate Test Data
declare @OrderMissing as table(
KeyNoteint,
Qtyint
)
insert into @OrderMissing (KeyNote, Qty)
select 1234,3 union
select 1235,23 union
select 1783,5
declare @OrderHeaderMissing as table(
RowIdint,
KeyNoteint
)
--Helper Table
declare @Helper as table(
MaxQty int
)
declare
@max-2 as int
select @max-2 = max(Qty) from @OrderMissing
;with cte_base as (
select 1 as Num union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 0
)
insert into @Helper (MaxQty)
select
one.Num + (ten.Num * 10) as MaxQty
from cte_base as one
cross join cte_base as ten
where
one.Num + (ten.Num * 10) <= @max-2
and one.Num + (ten.Num * 10) > 0
/*
** Please note that I am adding upto 23 since the sample data you have provided had the 23 as max. But if you have more than that in the
production environment make sure to add hundreds, thousands also.
e.g :
select one.Num + (ten.Num * 10) + (ten.Num * 10) + (hundred.Num * 100)
from cte_base as one
cross join cte_base as ten
cross join cte_base as hundred
<where_condition>
*/
-------------------------------------------------------------
;with cte_missing as (
select
row_number() over(order by A.KeyNote) as RowId,
row_number() over(partition by A.KeyNote order by A.KeyNote) as SubId,
A.KeyNote
from @OrderMissing as A
join @Helper as H on H.MaxQty <= A.Qty
)
insert into @OrderHeaderMissing (RowId, KeyNote)
select M.RowId,M.KeyNote
from cte_missing as M
select * from @OrderHeaderMissing
Hope this will solve your porblem. 🙂
January 17, 2012 at 9:23 pm
Here is another option.
-- Samle working table
CREATE-- DROP
TABLEOrdersMissing
(
OrderNumINTEGERNOT NULL,
MissingValuesINTEGERNOT NULL
)
-- Sample data
INSERT
INTOOrdersMissing
VALUES(1234, 3),
(1235, 23),
(1783, 5)
-- Produce one record for each item listed as missing.
SELECTm.OrderNum,
Tally.N,
'Other needed columns here...'
FROMOrdersMissing m
JOIN(
SELECTROW_NUMBER() OVER (ORDER BY id) AS N
FROMsys.sysobjects
) tally
ONTally.N <= m.MissingValues
Please note I have used a shortcut to build a tally table that will have a varying number of rows depending on your server. If the values for your missing orders are below 100, you should be fine with this, but I recommend searching for tally tables samples. You can either build a permanent one or use a CTE to build a dynamic one with any number of records to suit your need.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply