Loop help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Loop help

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-01-07 : 13:09:27

Hi All,

I'm trying to split a order into difrent orders based on pack location (Packid)

DECLARE @counter AS INT

SELECT T_Product_Option.PackID
FROM T_Order_Detail INNER JOIN
T_Product_Option ON T_Order_Detail.OptionID = T_Product_Option.id
WHERE (T_Order_Detail.OrderID = 552935) and
(NOT (dbo.T_Product_Option.id IN
(SELECT optionid
FROM dbo.T_Product_Free)))
GROUP BY T_Product_Option.PackID

SELECT @counter=@@ROWCOUNT

IF @counter>1

-- I Need to loop thru this part for each group of packid

INSERT INTO T_Order_Main
(CustomerID, Orderdate, IP, Orderstatus, SiteID, CouponID)
SELECT CustomerID, Orderdate, IP, Orderstatus, SiteID, CouponID
FROM T_Order_Main AS T_Order_Main_1
WHERE orderid=@orderid

INSERT INTO T_Order_Ship
(OrderID, shipping, payment, referid, remark, packaging)
SELECT @@identity, shipping, payment, referid, remark, packaging
FROM T_Order_Ship AS T_Order_Ship_1
WHERE orderid=@orderid

INSERT INTO T_Order_Remark
(orderid, Remarkid, remarktxt)
SELECT @@identity, Remarkid, remarktxt + 'Splited From: ' + @orderid
FROM T_Order_Remark AS T_Order_Remark_1
WHERE orderid=@orderid

-- I Need to do a SUB loop for each product in from the packid

INSERT INTO T_Order_Detail
(OrderID, ProductID, OptionID, Quantity, Cost, Discount)
SELECT @@identity, ProductID, OptionID, Quantity, Cost, Discount
FROM T_Order_Detail AS T_Order_Detail_1
WHERE orderid=@orderid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 23:46:28

how do you relate packid to other tables? can you explain with some sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources