inserting multiple records from 1 record
-
Hi Everyone
I need to insert pricing breakdown information into table B from table A
Table A looks like this:
ItemNumber
BreakQty1
BreakQty2
BreakQty3
BreakQty4
BreakQty5
Discount1
Discount2
Discount3
Discount4
Discount5
Table B looks like this
IdDiscount
ItemNumber
QuantityFrom
QuantityUntil
DiscountPerunit
Num
The data in table A represents the ItemNumber and the price breakdown per quantity in one record.
Means:
From:
1 to (BreakQty1) reduce the price by Discount1
(BreakQty1 + 1) to (BreakQty2) reduce the price by Discount2
(BreakQty2 + 1) to (BreakQty3) reduce the price by Discount3
(BreakQty3 + 1) to (BreakQty4) reduce the price by Discount4
(BreakQty4 + 1) to (BreakQty5) reduce the price by Discount5
The goal is to write it into Table B but instead of writing it as one record, I need to write it at a Maximum of 5 records or if BreakQty = 9999999 (as that Itemnumber may not have 5 price breakdowns)
Sorry, I forgot to mention that the NUM represents a counter for each line written and getting reset after each record.
Means it will go up from 1 to 5 and then start again after the next record will be transformed.
Any ideas how to write each record in table A as 1-5 records in Table B
Thanks a lot.
Oren Levy
-
Use 5 INSERT statements.
-
How do I write 5 insert statements against 1 select?
-
You need 5 SELECTs also.
If you want to insert 1 to 5 records, you need 5 INSERT statements.
or
You can use UNION.
-
INSERT INTO TableB
SELECT 1, BreakQty1, Discount1 FROM TableA WHERE BreakQty1 IS NOT NULL -- or you condition
UNION SELECT BreakQty1 + 1 , BreakQty2, Discount2 FROM TableA WHERE BreakQty2 IS NOT NULL -- or you condition
UNION SELECT BreakQty2 + 1 , BreakQty3, Discount3 FROM TableA WHERE BreakQty3 IS NOT NULL -- or you condition
UNION SELECT BreakQty3 + 1 , BreakQty4, Discount4 FROM TableA WHERE BreakQty4 IS NOT NULL -- or you condition
UNION SELECT BreakQty4 + 1 , BreakQty5, Discount5 FROM TableA WHERE BreakQty5 IS NOT NULL -- or you condition
-
Look into UNPIVOT in BOL.
DROP
TABLE #tblA
CREATE
table #tblA
(
ItemNumber INT,
BreakQty1 INT,
BreakQty2 INT,
BreakQty3 INT,
BreakQty4 INT,
BreakQty5 INT,
Discount1 INT,
Discount2 INT,
Discount3 INT,
Discount4 INT,
Discount5 INT
)
INSERT
INTO #tblA
VALUES(1,10,20,30,40,50,100,200,300,400,500)
INSERT INTO #tblA
VALUES(2,11,21,31,41,51,101,201,301,401,501)
INSERT INTO ...
SELECT
...
FROM
(
SELECT * FROM #tblA) AS a
UNPIVOT
(
BreakQtyValue FOR BreakQtyID IN (BreakQty1,BreakQty2,BreakQty3,BreakQty4,BreakQty5)
) AS b
UNPIVOT
(
DiscountValue FOR DiscountID IN (Discount1,Discount2,Discount3,Discount4,Discount5)
) AS b
Note: This is a "CROSS JOIN" So if more than 1 of BreakQty or Discount is NOT NULL in a row, you will get more than the desired 5 rows. (up to 25 (5x5))
-
Ok. I Figured out what you want:
DROP
TABLE #tblA
CREATE
table #tblA
(
ItemNumber INT,
BreakQty1 INT,
BreakQty2 INT,
BreakQty3 INT,
BreakQty4 INT,
BreakQty5 INT,
Discount1 INT,
Discount2 INT,
Discount3 INT,
Discount4 INT,
Discount5 INT
)
INSERT
INTO #tblA
VALUES(1,10,20,30,40,50,100,200,300,400,500)
INSERT INTO #tblA
VALUES(2,11,21,31,41,51,101,201,301,401,501)
SELECT -- *
ROW_NUMBER() OVER (ORDER BY ItemNumber) AS IdDiscount,
CASE DiscountID
WHEN 'Discount1' THEN 1
WHEN 'Discount2' THEN BreakQty1 + 1
WHEN 'Discount3' THEN BreakQty2 + 1
WHEN 'Discount4' THEN BreakQty3 + 1
WHEN 'Discount5' THEN BreakQty4 + 1 END AS DiscountFrom,
CASE DiscountID
WHEN 'Discount1' THEN BreakQty1
WHEN 'Discount2' THEN BreakQty2
WHEN 'Discount3' THEN BreakQty3
WHEN 'Discount4' THEN BreakQty4
WHEN 'Discount5' THEN BreakQty5 END AS DiscountTo,
ItemNumber,DiscountPerUnit,
DENSE_RANK() OVER (ORDER BY DiscountID) AS Num
FROM
(
SELECT *
FROM #tblA) AS a
UNPIVOT
(
DiscountPerUnit FOR DiscountID IN (Discount1,Discount2,Discount3,Discount4,Discount5)
) AS b
ORDER BY IdDiscount
Pretty sure that will give you what you are looking for
-
This is an SQL Server 2000 forum... and there's no UNPIVOT in SQL Server 2000
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
oh well. That makes it hard
Because there isn't a row_number, or dense_rank either. Hmm. I often forget where I am, and don't have 2000 instance anymore to even play.
Set the ItemNumber column in the table as ... INT IDENTITY(1,1)
Change the unpivot to 5 UNION ALL statements. You can replace the DENSE_RANK statements with Hard_Coded numbers. CASE Statements will be the same
INSERT INTO ....
select ..., 1 NUM
WHERE Discount1 IS NOT NULL
UNION ALL
select ..., 2 NUM
WHERE Discount2 IS NOT NULL
-
try this...it should give you want you want. I'm sure it can be improved upon, but it's a starting point.
--drop table #tableA
CREATE table #tableA
(
ItemNumber int identity not null,
BreakQty1 int,BreakQty2 int,BreakQty3 int,BreakQty4 int,BreakQty5 int,
Discount1 money,Discount2 money,Discount3 money,Discount4 money,Discount5 money
)
insert into #tableA
values(10,20,30,40,50,0.0,3.0,4.0,6.0,7.5)
insert into #tableA
values(15,25,40,60,80,0.0,2.5,3.5,4.75,5.5)
insert into #tableA
values(5,10,15,25,50,1.0,2.25,3.5,6.0,8.0)
insert into #tableA
values(5,50,9999999,0,0,1.0,2.25,4.5,0.0,0.0)
insert into #tableA
values(50,9999999,0,0,0,2.0,6.0,0.0,0.0,0.0)
-- drop table #tableB
create table #tableB
(
IDDiscount int identity(1,1),
ItemNumber int,
QuantityFrom int,
QuantityTo int,
DiscountPerUnit money,
Num int
)
insert into #tableB
select ItemNumber = a.ItemNumber, QuantityFrom = 1, QuantityTo = BreakQty1,DiscountPerUnit = Discount1, Num = 1
from #tableA a
union
select ItemNumber = a.ItemNumber, QuantityFrom = BreakQty1+1, QuantityTo = BreakQty2,DiscountPerUnit = Discount2, Num = 2
from #tableA a where isnull(BreakQty1,0) <> 0 and isnull(BreakQty2,0) <> 0
union
select ItemNumber = a.ItemNumber, QuantityFrom = BreakQty2+1, QuantityTo = BreakQty3,DiscountPerUnit = Discount3, Num = 3
from #tableA a where isnull(BreakQty2,0) <> 0 and isnull(BreakQty3,0) <> 0
union
select ItemNumber = a.ItemNumber, QuantityFrom = BreakQty3+1, QuantityTo = BreakQty4,DiscountPerUnit = Discount4 ,Num = 4
from #tableA a where isnull(BreakQty3,0) <> 0 and isnull(BreakQty4,0) <> 0
union
select ItemNumber = a.ItemNumber, QuantityFrom = BreakQty4+1, QuantityTo = BreakQty5,DiscountPerUnit = Discount5, Num = 5
from #tableA a where isnull(BreakQty4,0) <> 0 and isnull(BreakQty5,0) <> 0
select * from #tableA
--delete from #tableB where QuantityFrom > @MaxQty or isnull(QuantityTo,0) < isnull(QuantityFrom,0)
select * from #tableB order by itemNumber, num
declare @ItemNumber int set @ItemNumber = 2
declare @quantity int set @quantity = 45
select discountPerUnit from #tableB where ItemNumber=@ItemNumber and @quantity between isnull(QuantityFrom,0) and isnull(QuantityTo,0)
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply