Insert unknown amount of records into table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert unknown amount of records into table

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-11-27 : 07:16:31

Hi,

I have a table PlanOrders with information for a specific idno.

I need to get the information from that table and insert a row into Orders with that idno. OrderNo is an identity column.

I need to then retrieve the OrderNo from Orders and insert that OrderNo into table OrderDetails.

PlanOrders .Track 1= OrderDetails.orderNoplan 1

PlanOrders .track 2= OrderDetails.orderNoplan2

PlanOrders .track 3=OrderDetails.orderNoplan 3

For each value in PlanOrders i need to insert 3 records for the OrdeNo.

create table #PlanOrders (
idno int,
track1 int,
track2 int,
track3 int,
)
insert into #PlanOrders (idno,track1,track2,track3) values (1,30,40,50)
insert into #PlanOrders (idno,track1,track2,track3) values (2,0,0,100)
insert into #PlanOrders (idno,track1,track2,track3) values (3,30,10,40)
insert into #PlanOrders (idno,track1,track2,track3) values (4,0,0,0)

CREATE TABLE [dbo].Orders(
[OrderNo] [int] IDENTITY(1,1) NOT NULL,
[IdNo] [int] NOT NULL,
[CreateDate] [smalldatetime] NOT NULL)
CREATE TABLE [dbo].[OrderDetails](
[OrderNo] [int] NOT NULL,
[OrderNoPlan] [smallint] NOT NULL,
[RubricNo] [tinyint] NOT NULL,
[OrderNoPercent] [float] NOT NULL)Since idno 1 has 3 values in table PlanOrders I need to first insert the 3 values into OrderDetails according to their track (if track1 has a value<>0 then under orderplan I insert 1 and orderNoPercent=30) I need to insert the same data 3 times for each RubricNo (11,12,13) as in example below. How can I achieve this? Thanks

orderno OrderNoPlan RubricNo OrderNoPercent
1 1 11 30
1 1 12 30
1 1 13 30
1 2 11 40
1 2 12 40
1 2 13 40
1 3 11 50
1 3 12 50
1 3 13 50
2 3 11 100
2 3 12 100
2 3 13 100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 11:33:50

[code]
SELECT idno AS orderno,
REPLACE(Track,'Track','') AS OrderNoPlan,
RubricNo,
Val AS OrderNoPercent
FROM
(
SELECT idno,Track,Val
FROM #PlanOrders po
UNPIVOT(Val FOR Track IN ([Track1],[Track2],[Track3]))u
)m
CROSS JOIN (VALUES(11),(12),(13)) n (RubricNo)
WHERE Val > 0
ORDER BY orderno,
OrderNoPlan,
RubricNo
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-12-01 : 01:52:05

Thanks. It works.
Can you please explain why you used cross join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-01 : 03:58:48

quote:
Originally posted by collie

Thanks. It works.
Can you please explain why you used cross join?

you need cross join for repeating each record with RubricNos 11,12 & 13. cross join will take cartesian product and repeats every row in resultset 3 times 1 for each rubricno

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-12-03 : 03:44:10

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-03 : 06:30:23

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources