An alternative to cursor?

• Coriolan

  SSCommitted

  Points: 1931

  More actions

  September 6, 2009 at 5:29 pm

  #134910

  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!

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  September 6, 2009 at 5:37 pm

  #1049933

  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]

• Coriolan

  SSCommitted

  Points: 1931

  More actions

  September 7, 2009 at 8:30 am

  #1050136

  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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  September 7, 2009 at 8:37 am

  #1050140

  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]

• Coriolan

  SSCommitted

  Points: 1931

  More actions

  September 7, 2009 at 4:43 pm

  #1050252

  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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  September 7, 2009 at 5:13 pm

  #1050255

  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]

• Coriolan

  SSCommitted

  Points: 1931

  More actions

  September 8, 2009 at 8:13 am

  #1050537

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