LuckyLuke82 LuckyLuke82 - 1 year ago 103 Question

SQL INSERT SELECT from Table2 to Table3

I'm trying to insert ID from Table1 and Table2 into Table3.My table design is:

field1 etc.

field1 etc.


I do this when I save records for Table1. Meanwhile I have Datagridview in my form, which uses comboboxcell to view records from Table2. When I select some record from Table2, both ID's (Table1 & Table2) needs to be inserted into Table3.

Here is my code so far (obviously wrong):

INSERT INTO Table3 (Id_Table3, Id_Table1, Id_Table2)

SELECT Id_Table2 FROM Table2 WHERE serial_no=" & MyDGV.CurrentRow.Cells(0).Value.ToString

VALUES (Id_Table3_seq.nextval, Id_Table1_seq.currval,????)

So, my problem stucks with Id_Table2 which needs to be selected first by Datagridview.Row cell value, and then result passed like a parameter to Table3. Any suggestions ? Probably an easy one, I just don't know how to start...

Answer Source

If I got your point right, this is what you need maybe?

INSERT INTO Table3 (Id_Table3, Id_Table1, Id_Table2) 
VALUES (Id_Table3_seq.nextval, Id_Table1_seq.currval,(
SELECT Top 1 Id_Table2 FROM Table2 WHERE serial_no='" & MyDGV.CurrentRow.Cells(0).Value.ToString & "'

Edited: Added Top 1 to make sure the subquery will only return 1 recordset. It's optional if the field you are selecting is unique.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download