Need help in writting SQL
-
Hi,
I need help in writting a T-SQL query. I have attached script for the schema and sample data. I have three tables SalesOrder, SalesOrderItems and SalesOrderSubItems.
SalesOrder - stores sales order information
SalesOrderItems - stores items assciated with sales order
SalesOrderSubItems - stores subitems assciated with each item
Suppose data in three tables is as shown below.
We have "Replicate" functionality in the application which creates duplicate salesorder in the system. So I would like to write a single query which will create new sales order in the database with Items and subitems in corrosponding tables.
Could anybody help here in writting sample query in simplest way ? I tried couple of thing but I was not able to avoid use of cursor for inserting the subitems of the salesorder item.
-
Here's one way. This is assuming that ItemName can uniquely identify the items.
DECLARE @NewSN nvarchar(50),@OldSalesOrderID int,
@NewSalesOrderID int
SELECT @OldSalesOrderID = 1, -- Order to be Copied
@NewSN = 'AFFI' -- SN for the new Order (May not be Needed)
INSERT INTO dbo.SalesOrder ( SerialNum, CustomerRef, ATTN )
SELECT @NewSN, CustomerRef, ATTN
FROM dbo.SalesOrder
WHERE SalesOrderId = @OldSalesOrderID
SELECT @NewSalesOrderID = SCOPE_IDENTITY()
INSERT INTO dbo.SalesOrderItems ( SalesOrderId, ItemName )
SELECT @NewSalesOrderID, ItemName
FROM dbo.SalesOrderItems
WHERE SalesOrderId = @OldSalesOrderID
;WITH
A AS (
SELECT ItemName, SalesOrderItemID
FROM dbo.SalesOrderItems
WHERE SalesOrderId = @NewSalesOrderID),
B AS (
SELECT ItemName, SalesOrderItemID
FROM dbo.SalesOrderItems
WHERE SalesOrderId = @OldSalesOrderID),
C AS (
SELECT A.ItemName, A.SalesOrderItemID NewSOID, B.SalesOrderItemID OldSOID
FROM A
INNER JOIN B ON A.ItemName = B.ItemName)
INSERT INTO dbo.SalesOrderSubItems ( SalesOrderItemId, SubItemName )
SELECT C.NewSOID, SI.SubItemName
FROM C C
LEFT JOIN dbo.SalesOrderSubItems SI ON C.OldSOID = SI.SalesOrderItemId
SELECT * FROM dbo.SalesOrder
SELECT * FROM dbo.SalesOrderItems
SELECT * FROM dbo.SalesOrderSubItems
-
Garadin (6/17/2011)
Here's one way. This is assuming that ItemName can uniquely identify the items.The fact that he's COPYING the records including the ItemName guarantees that the ItemName cannot uniquely identify the items.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Sorry, It doesn't need to uniquely identify rows in the table, just the items on the order.
I'm assuming the real table is more complex, perhaps with an ItemNumber, or Qty, or some such. If there's an ItemNumber that is unique to the actual item while the ItemName is just something more general like 'SCREW' and you have a bunch of those, you'd want to use ItemNumber.
-
rajg (6/17/2011)
So I would like to write a single queryWhy does it have to be a "single query"?
--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)
Intro to Tally Tables and Functions -
Hi Garadin,
Thanks for the query. Yes, there is a column "SerialNumber" which can uniquely identiy the items within Salesorder. I think that can be used.
@jeff - sorry, my mistake in post. It doesnt have to be single query. I just wanted have it simple.
I think above query will work. When I tried, my approach was to get new ItemIds and corrosponding old ItemIds in some table variable. But with the below query, I think I can get only the new Id's since output clause will not allow me to get ids from the select statement which are being replicated :
INSERT INTO SalesOrderItemOUTOUT inserted.SalesOrderItemId INTO @SomeTableVariable
SELECT SalesOrderId, ItemName
FROM SalesOrderItem
WHERE SalesOrderId = 1
Thanks all for you help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply