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 @NewSNnvarchar(50),

    @OldSalesOrderIDint,

    @NewSalesOrderIDint

    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

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • rajg (6/17/2011)


    So I would like to write a single query

    Why 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)

  • 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 SalesOrderItem

    OUTOUT 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