dwilliscp (7/25/2014)
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.
Ideas?
You have one table... lets call it Resource_Mat. PK Company, Plant, Resource, Part Number. What I need to do is ..
Get the first row from Resource_mat and for every row in the table create a data set that contains:
Company, Plant, Resource, [Selected rows' part number], [current rows' part number]
The only restriction is that the loop needs to only work on where Comany + Plant + Resournce matches between selected row and current row.
This is why I thought about using a cursor.. but the data set is so large, and SQL will throw it all on one CPU... I expect it to take too long.
The result of the above query then gets updated/inserted into a table that holds this information about from part .. to part and then we keep up how long it takes to transitition from one part number to a different part number.
This is NOT the time to use a cursor. You really need to change the way you think about data. You have to think about what you want to do to a column, not a row.
If you can post ddl and sample data along with desired output we can show you how to do this in a set based manner.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/