Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table called service entry which has a field called part description. I am trying to normalize my table structures.

**Dbo.ServiceEntry

  ServiceEntryID, Description
     1200         270 ~ Syringe assembly  ~ 7,3 ~ Increase drive current from 3 --> 1.|282 ~ LPH pcb ~ 8 ~  | 287 ~ Valve block
   assembly ~ null ~

Sample data for the part description column:

**270 ~ Syringe assembly ~ 7,3 ~ Increase drive current from 3 --> 1.|282 ~ LPH pcb ~ 8 ~ | 287 ~ Valve block assembly ~ null ~

In the above here is the structure for the column: PartID ~ PartDescription ~ ServiceType ~ Comment

For multiple parts, this character | is added and the structure is repeated.

Now the other table Service Entry Part has a one to many relationship for instance a Service Entry could have multiple Service entry parts.

Dbo.ServiceEntryPart
ID,ServiceEntryID,PartID,Comment,ServiceType

New Update to the table ServiceEntryPart will contain a column called Part Description. so it will look like below

**Dbo.ServiceEntryPart
ID,ServiceEntryID,PartID,Comment,ServiceType,PartDescription**

Existing Data in ServiceEntryPart:

ID  ServiceEntryID  PartDescription PartID  ServiceType Comment
1    1200                            270      7,3        Increase drive current from 3
2    1200                            282      8          
3    1200                            287      null      

Desired Data in ServiceEntryPart

ID  ServiceEntryID  PartDescription  PartID    ServiceType   Comment
1    1200           Syringe assembly    270      7,3         Increase drive current from 3
2    1200           LPH pcb             282      8          
3    1200           Valve block         287      null      

So essentialy for the sake of normalization, I want to look at the existing service entries and within the description column, I want to parse out the part description and update the service entry part table with the appropriate part descriptions.

share|improve this question

closed as too localized by Mark Storey-Smith, Jon Seigel, Paul White, Mike Walsh, Leigh Riffel Mar 19 '13 at 19:24

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Where does ~ L0 ~ P0 come from? –  Aaron Bertrand Feb 12 '13 at 18:07

1 Answer 1

Stop thinking about storing a description in a single variable and updating each row one by one. SQL Server is optimized to work on sets; so think about applying an update to all rows at once. Note that I made a guess about where L 0 ~ P 0 comes from (hard-coded). If this is something real, please include that information in the question.

UPDATE se SET PartDescription = STUFF(
  (SELECT '| ' + CONVERT(VARCHAR(12), p.ID) 
       + ' ~ ' + p.Description + ' ~ L 0 ~ P 0 '
    FROM dbo.Part AS p 
    INNER JOIN dbo.ServiceEntryPart AS sep
    ON p.ID = sep.PartID
    AND p.OldPartID = sep.OldPartID
    WHERE sep.ServiceEntry = se.ID
    ORDER BY p.ID FOR XML PATH, TYPE
  ).value('.[1]', 'varchar(max)'), 1, 2, '')
FROM dbo.ServiceEntry AS se;
share|improve this answer
    
5000 ~ New Slide Description ~ 7 ~ Increase drive current from 3 --> 1.| 5500 ~ Xyz ~ ~ 8 ~ | 5510 so in this column the first 5000 is the id and the description new slide description is the part description. so for each part im just trying to update the id and part description –  user19096 Feb 12 '13 at 18:42
1  
@user19096 ugh. –  Aaron Bertrand Feb 12 '13 at 18:43
    
Im sorry,I updated the question. Would you think I should add the column part description in the serivce entry part table. And then based on the existing records populate that column –  user19096 Feb 12 '13 at 18:49
    
Yes, you should build up the information in the concatenated column completely with all the information you have RIGHT NOW. Or better yet, don't store it there, just create a view that puts it together at runtime, then you don't have to update this again tomorrow or next week when more parts change. Your application isn't going to care when the concatenation happened... –  Aaron Bertrand Feb 12 '13 at 18:52
    
so if i try to update part description column in the normalized service entry table, atleast once I have to populate it manually and in the future the app code will populate that column. so for this data 270 ~ Syringe assembly (piston and barrel) ~ 7 ~ Increase drive current from 3 --> 1.| 282 ~ LPH pcb ~ 8 ~ | 287 ~ Valve block assembly ~ 8 ~ | 290 ~ Syringe assembly (piston and barrel) ~ 7 ~ Increase drive current from 3 --> 1 How could I update the existing service entry part table with the part description which is the after the first ~. –  user19096 Feb 12 '13 at 18:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.