SQL - How can I INSERT INTO Auto populated incremented field FROM another table

[M4A]

Hi all, i have a simple DB with 2 tables:Table1.....ID|1st Lot No|No of Blocks|Part Number|Date|Lot Qty.Table2.....ID|---------Lot No-----------|Part Number|Date|Lot Qty.What im trying to achieve is When the user inputs values in Table1, the data is pulled through to Table2, but i need the value in [Table2,Lot No] to auto increment Based on the Table1.No of blocks.e.g. User inputs:ID|1st Lot No|No of Blocks|Part Number|Date |Lot Qty.1 | 62204 | 6 | TR456 |1/1/12|2.978KMThen when Table1 Row_inserted, Table2 is auto updated to:ID| Lot No|Part Number|Date |Lot Qty1 |62204 | TR456 |1/1/12 |2.978KM2 |62205 | TR456 |1/1/12 |2.978KM3 |62206 | TR456 |1/1/12 |2.978KM4 |62207 | TR456 |1/1/12 |2.978KM5 |62208 | TR456 |1/1/12 |2.978KM6 |62209 | TR456 |1/1/12 |2.978KMI am using aspmaker and putting the code in the server event:' Row Inserted eventSub Row_Inserted(rsold, rsnew) End SubI have been trying to achieve this using dims, insert into, i'v done numerouse tutorials and i just cant get it to work. My sql knowledge is moderate. But i'v been stuck on this for about 200hrs NO JOKE, i dont like giving up.Please If anyone has the skills to show me the way, then i would greatly appreciate your help.Regards, Mike.

[justsomeguy]

This isn't a SQL-only solution necessarily. It's probably possible to do all of this in SQL using a user-defined function or something similar, but I would use another language. It sounds like all you need to do is get the lot number and the quantity, and write a loop for however many the quantity is set to that will increment the lot number and add a row to the table. What code do you have now for inserting the one row?

[M4A]

This isn't a SQL-only solution necessarily. It's probably possible to do all of this in SQL using a user-defined function or something similar, but I would use another language. It sounds like all you need to do is get the lot number and the quantity, and write a loop for however many the quantity is set to that will increment the lot number and add a row to the table. What code do you have now for inserting the one row?

' Row Inserted eventSub Row_Inserted(rsold, rsnew)Dim InsertSQLInsertSQL = ("INSERT INTO reelers (LotNumbers, [Part Number], [Lot Qty], Description, [Manual Lot Number], Notes) SELECT FirstLotNumber, [Part Number], [Lot Qty], Description, [Manual Lot Number],Notes FROM reelersinput;")Dim ResultResult = ew_Execute(InsertSQL)End Sub

[justsomeguy]

OK, that's just going to insert a single row. The number of rows you need to insert is based on the quantity, and the lot number needs to change each time. So you need to do one query where you select all of the data that you just inserted (if you don't have it somewhere else), and write a for loop to loop through the quantity and update the lot number each time to insert the next row. This page describes loops in VBScript: http://www.w3schools.com/vbscript/vbscript_looping.asp So you would select your data, get the lot number and quantity, and start the loop to insert new records. e.g.:

for i = 1 to quantity  lot_nr = lot_nr + 1  ' insert new record with the updated lot number and the rest of the data from the original recordnext

[M4A]

I got it working! heres the code: ' Row Inserted eventSub Row_Inserted(rsold, rsnew)Dim InsertSQL, LotNo, BlockNo, iLotNo = RsNew("LotNumber")BlockNo = RsNew("NoofBlocks")For i = 1 to BlockNo InsertSQL = ("INSERT INTO reelers (LotNumber, NoofBlocks, [Part Number], [Lot Qty], Description, [Manual Lot Number], Notes) SELECT " & LotNo & ", NoofBlocks, [Part Number], [Lot Qty], Description, [Manual Lot Number],Notes FROM reelersinput;")ew_Execute(InsertSQL) LotNo = LotNo + 1NextEnd Sub

[justsomeguy]

The only thing I don't like there is that you're using a select statement in your insert query but not telling it which record to select, it could be duplicating any data from your table with the wrong lot numbers. If the original data is in RsNew, then I would recommend you use that data in the insert query instead of selecting another row.