SQL INSERT SELECT from Table2 to Table3

Question

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

Table1:
Id_Table1
field1 etc.

Table2:
Id_Table2
field1 etc.

Table3:
Id_Table3
Id_Table1_FK
Id_Table2_FK

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...

My question may be stupid but can you be sure that your select query would return exactly 1 value? may it return no value? — romulus001, Oct 17 '16 at 08:04
@romulus001, no It can't return no value. This INSERT occurs only If you select some record from Comboboxcell in Datagrid. — LuckyLuke82, Oct 17 '16 at 08:05
@romulus001, regarding that, I already added Top 1 to the query to make sure that it will only return one recordset. But is optional if the field is unique. (as I've mentioned in my edit). — Aethan, Oct 17 '16 at 08:07
@Crush Sundae : I preferred to ask that as I'm currently migrating microsoft access datatables to microsoft sql server, I made a similar query as your initial one but I failed at running it, adding `TOP 1` in my select queries solved my problem — romulus001, Oct 17 '16 at 08:13

Aethan · Accepted Answer · 2016-10-17T08:05:30.287

2

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.

edited Oct 17 '16 at 08:05

answered Oct 17 '16 at 07:53

Aethan

1,986
2
18
25

1

thanks, you understood me ! I didn't knew I could write SQL like this too. – LuckyLuke82 Oct 17 '16 at 08:04
Please use SQL parameters instead of a string. – Alex B. Oct 17 '16 at 08:07
@AlexB., how ? Any why, is It better ? – LuckyLuke82 Oct 17 '16 at 08:08
@LuckyLuke82, one of its use is to avoid [SQL Injections](http://www.acunetix.com/websitesecurity/sql-injection/). – Aethan Oct 17 '16 at 08:09
2

@AlexB,@Crush Sundae - If you meant like this I allready have it - cmd.Parameters.Add(New OracleParameter("Id_Table2", row.Cells(0).Value.ToString)). But then I will use this parameter as code provided – LuckyLuke82 Oct 17 '16 at 08:10
1

`TOP 1` is invalid for Oracle – Oct 17 '16 at 09:06
@a_horse_with_no_name, thanks. I used It without anyway. – LuckyLuke82 Oct 17 '16 at 09:10

score 0 · Answer 2 · answered Oct 17 '16 at 09:11

Use a SELECT that returns constant valus:

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

^{In general you should not concatenate values into a SQL string. Use a prepared statement with parameters (placeholders) instead (don't know how that is done in VB.net)}

SQL INSERT SELECT from Table2 to Table3

2 Answers2