Jeff Moden (7/28/2014)

---

dwilliscp (7/27/2014)

---

Jeff Moden (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?

Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.

Also, I'd personally like to know what it is you mean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.

Jeff... they are attached... Ok we have a file that shows all resources in a plant, and the materials that we use in them, during production. What we are trying to do, since our shop floor planning software does not do it, is to go in and generate a list of how long it takes to change a resource from producing part A to part B. So I need to take the list of parts, for each company + plant + resource... and create the from part ... to part. Then we can go in and assign how long it will take to transition from part A to Part B... before we can start to produce part B. Got it? The cursor... that I posted on friday.. ran until I killed it on Sunday Morning... and I was running for only one plant.. and parts that started with Z. This was a list of 362 rows.

Maybe it's just these old eyes but I see a cursor and sample data with "Material_Number" in them but no "Part_Number". Is "Material_Number" what you're talking about?

Also, I'm still not bagging what you're raking about the From_Material and To_Material. To me, your cursor...

declare @plant as varchar(4)

, @Resource_Code as varchar(8)

, @Material_Number as varchar(18)

set nocount on;

declare i_cursor insensitive cursor

for Select Plant, Resource_Code, Material_Number From Resource_Mat

open i_Cursor

while @@fetch_status = 0

begin

insert into trans_Matrix(Company, Plant, Resource_Code, From_Material, To_Material)

select 'SPOR' as Company

, Plant

, Resource_Code

, @Material_Number as From_Material

, Material_Number as To_Material

from Resource_Mat

where @Plant = plant and @resource_code = resource_code

end

fetch next from i_Cursor into @Plant, @Resource_Code, @Material_Number

close i_Cursor

deallocate i_cursor

set nocount off;

go

... looks like a massive CROSS JOIN of the Resource_Mat table to itself based on the "plant" and "resource_code". While that doesn't make sense to me (even though you mentioned that in the title of the post), if that's what you really need, the following code should do it for you...

INSERT INTO dbo.Trans_Matrix

(Company, Plant, Resource_Code, From_Material, To_Material)

SELECT Company = 'SPOR'

,Plant = frommat.Plant

,Resource_Code = frommat.Resource_Code

,From_Material = frommat.Material_Number

,To_Material = tomat.Material_Number

FROM dbo.Resource_Mat frommat

JOIN dbo.Resource_Mat tomat --This is going to work like a conditional CROSS JOIN

ON frommat.Plant = tomat.Plant

AND frommat.Resource_Code = tomat.Resource_Code

;

... and, compared to the cursor, it will run like the wind generating millions of rows in just a couple of seconds if you have some decent indexing on the Resource_Mat table.

Of course, you never answered my question about what you meant by a "massive" table. How many rows does it actually have in it???

Last but not least, if you go back and look at HanShi's post (the second post on this thread), he suggested the exact same thing I just did. You just needed to study the code a bit to realize that it's probably exactly what you wanted sans some of your column names and the missing criteria for the Resource_Code.