Incremental data duplication

[SqlHunter]

I have a table with id and its duplication that should be used in table 2.Now I have table generated with the duplication_id for the corressponding id.

Table1

id Duplication_id
-------------------------
1A 500
1A 501
1A 502
7D 503
7D 504
9G 505
9G 506


Now the pkey is the primary key and its not a auto number so when i duplicate i should be able to do max(key)+1 so that as u duplicate just the data for each series,the key get incremented each time.The final out out looks like as given below



Table 2

key id item
--------------------------
1 1A cell
4 7D watch
6 9G camera

7 500 cell
8 501 cell
9 502 cell
10 503 watch
11 504 watch
12 505 camera
13 506 camera

please help me out

 

[johpje]

Hi,

Best to make a Stored Procedure where you create a cursor
SELECT duplication_id, item
FROM Table2
INNER JOIN Table1
ON (Table1.[id] = Table2.[id])

Then you do an insert (with new id) for each record in the cursor.

Not recomended because the 500 is fixed that way, but for the example you gave you could also use:
INSERT INTO Table2
SELECT ((duplication_id-500) + (SELECT MAX([key]) FROM Table2)), duplication_id, item
FROM Table2
INNER JOIN Table1
ON (Table1.[id] = Table2.[id])

Regards
Johan