An alternative to cursor?

  • I have a table called Machine with a primary key MachineId. Each machine has attributes such as serial number, etc...

    Technically, a machine order should have a machineID attached to it. But my conversion data comes with x number of orders without machineIDs.

    I can identify orders without machineIDs easily and need to assign machineIDs to them.

    So this is how I plan to proceed and am looking for better ideas:

    1. Given x number of orders without MachineIDs, generate x MachineIDs (via

    inserts on table Machine with OUTPUT inserted.* into @MachineIDstoAssign

    2. Cursor to assign MachineId from table variable @MachineIDstoAssign to the x number of orders that need MachineIds.

    Is there a better way to do this? Thanks!

  • If you can give us the table definitions and some sample data, we can show you how to do this.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi,

    Here is a simplistic sample of the two tables. Thanks

    Table1

    PrimaryKey: OrderID

    OrderId MachineID

    5000 NULL

    5100 NULL

    etc...

    Table2

    PrimaryKey MachineID

    MachineID Serial Number IsReturnable ....

    100 ABC123

    101 XYZ123

    102 KY12345

    etc...

    I will insert x MachineID ( say from 1000, 1001, 1002 etc...) and will need Order 5000 associated with MachineID=1000, Order 5100 associated with MachineID=1001 etc... so that ALL machine orders will have their associated machine IDs.

    Thanks

  • Thank your for the table def.

    But we'd need some valid sample data as well.

    For details on how to do it please follow the link in my signature.

    When providing easy to use sample data you increase the number of volunteers looking at your issue.

    So please help us help you.

    Edit: To be a little more specific:

    Where do you get the new machine ID's including the other (required?) columns from?

    How are those IDs related to the current machine IDs (e.g. max(ID) + 1)?

    Does the order of machine IDs have to match the order of OrderId (e.g. both ascending)?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Lutz,

    Here is a sample of data. Let me know what else I should provide. Thanks!

    DECLARE @ListofOrderstoFix table

    (OrderID int NOT NULL,

    MachineID int NULL)

    create table #table1

    (OrderId int IDENTITY(1,1) NOT NULL,

    Customer int NOT NULL,

    machineID int NULL)

    Insert #table1 (customer,machineId)

    select 1,1000

    Insert #table1 (customer,machineId)

    select 2,1001

    Insert #table1 (customer,machineId)

    select 3,1002

    Insert #table1 (customer,machineId)

    select 4,null

    Insert #table1 (customer,machineId)

    select 5,null

    Insert #table1 (customer,machineId)

    select 6,null

    -- list all orders without machineIDs

    insert @ListofOrderstoFix

    Select OrderId, 0 from #table1 where machineID is null

    Select * from @ListofOrderstoFix

    create table #table2

    (machineID int IDENTITY(1000,1) NOT NULL,

    Serial varchar(50) NULL)

    Insert #Table2 (serial)

    select 'abc12345'

    Insert #Table2 (serial)

    select 'xy345'

    Insert #Table2 (serial)

    select 'AB345'

    Insert #Table2 (serial)

    select 'MN9887787'

    Insert #Table2 (serial)

    select '0'

    Insert #Table2 (serial)

    select '0'

    Insert #Table2 (serial)

    select '0'

    -- list of machineIDs to assign to orders

    Select * from #table2 where Serial = '0'

    drop table #table1

    drop table #table2

  • First of all, thank your for providing the sample data! Good job! Made it really easy to work on. 🙂

    Following please find a proposal of what I think might help you. The key is the Row_Number function of SS2K5: Both tables are numbered and then joined on the row number to perform the update.

    Note: the semicolon at the beginning of the statement (or rather the terminator of the previous statement) is mandatory.

    If there's anything I missed or you need addtl. explanation please let us know.

    ;WITH

    sub_orders AS (

    SELECTOrderId,

    ROW_NUMBER() OVER (ORDER BY OrderID) AS row

    FROM @ListofOrderstoFix

    ),

    sub_machines AS (

    SELECTmachineID,

    ROW_NUMBER() OVER (ORDER BY machineID) AS row

    FROM #table2

    WHERE Serial = '0'

    )

    UPDATE lo

    SET lo.MachineID = sm.machineID

    FROM sub_orders so

    INNER JOIN sub_machines sm ON so.row=sm.row

    INNER JOIN @ListofOrderstoFix lo ON so.OrderID=lo.OrderId

    SELECT * FROM @ListofOrderstoFix

    /* result set

    OrderIDMachineID

    41004

    51005

    61006

    */

    Edit: typo fixed.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thank you! It works and is an elegant and cleaner solution.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply