CODEWITHSUNDEEP
mysqlsql-serverprocedure
eto_donna
eto_donna

Reputation: 61

T-SQL query procedure-insert

I am wondering if any of you would be able to help me. I am trying to loop through table 1 (which has duplicate values of the plant codes) and based on the unique plant codes, create a new record for the two other tables. For each unique Plant code I want to create a new row in the other two tables and regarding the non unique PtypeID I link any one of the PTypeID's for all inserts it doesnt matter which I choose and for the rest of the fields like name etc. I would like to set those myself, I am just stuck on the logic of how to insert based on looping through a certain table and adding to another. So here is the data:

   Table 1                              
PlantCode    PlantID      PTypeID      
   MEX        1              10        
   USA        2              11        
   USA        2              12        
   AUS        3              13        
   CHL        4              14        



   Table 2                          
PTypeID     PtypeName    PRID       
 123         Supplier     1         
 23          General      2         
 45          Customer     3         
 90          Broker       4         
 90          Broker       5         


   Table 3
      PCreatedDate            PRID         PRName     
2005-03-21 14:44:27.157         1        Classification
2005-03-29 00:00:00.000         2        Follow Up
2005-04-13 09:27:17.720         3        Step 1
2005-04-13 10:31:37.680         4        Step 2
2005-04-13 10:32:17.663         5        General Process

Any help at all would be greatly appreciated

Upvotes: 0

Views: 35

Answers (1)

simon at rcl
simon at rcl

Reputation: 7344

I'm unclear on what relationship there is between Table 1 and either of the other two, so this is going to be a bit general.

First, there are two options and both require a select statement to get the unique values of PlantCode out of table1, along with one of the PTypeId's associated with it, so let's do that:

select PlantCode, min(PTypeId)
from table1
group by PlantCode;

This gets the lowest valued PTypeId associated with the PlantCode. You could use max(PTypeId) instead which gets the highest value if you wanted: for 'USA' min will give you 11 and max will give you 12.

Having selected that data you can either write some code (C#, C++, java, whatever) to read through the results row by row and insert new data into table2 and table3. I'm not going to show that, but I'll show how the do it using pure SQL.

insert into table2 (PTypeId, PTypeName, PRID)
select PTypeId, 'YourChoiceOfName', 24 -- set PRID to 24 for all
from
(
    select PlantCode, min(PTypeId) as PTypeId
    from table1
    group by PlantCode   
) x;

and follow that with a similar insert.... select... for table3.

Hope that helps.

Upvotes: 1

Related Questions