Create multiple entries based upon quantity

[WelshyWizard]

Hi all,

I have a SQL Server 2000 table that holds a long list of parts. Each row is show something like this:

ID Cont# Str# PartID GdrNo Qty Length Width Thick Grade
1 927 001 P12 A1 3 2100 250 12 S355J2G3
2 927 001 P13 A1 6 2100 250 12 S355J2G3
3 927 001 P15 A2 2 1100 250 15 S355J2G3
4 927 001 P15 A3 2 1100 250 15 S355J2G3
5 930 001 P12 A2 1 2000 500 10 S355J2G3

The key field here is the ID field which is an IDENTITY field.
I would like all of these items to be split out into seperate records depending upon their quantites in a 2nd table as shown below:

WeldNo ID WeldDet
1 1
2 1
3 1
4 2
5 2
6 2
7 2
8 2
9 2
10 3
11 3
12 4
13 4
14 5

The key field here is the WeldNo field which is an IDENTITY field. The two tables are linked together using the ID fields. The WeldDet field will be filled in by the user.
Ideally I would like to specify which parts this happens to by entering a cont# and str#.

Cheers.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[vbkris]

>>The WeldDet field will be filled in by the user.

this means that there will be an UI where users will enter data per row right? why not insert into the second table ONLY after the details in the UI are entered?

as currently it will be more effecient to replicate rows on the UI and based on the count in the UI do inserts.

the only way that you can do this in a DB is to use cursors / while loops...

Known is handfull, Unknown is worldfull