Insert Into with value select from the same table

Question

I have a table [named] Application_Form_Controls_Management which I would like to insert into it values for 19 columns, 3 of those columns (App_Form_Name,App_Form_Function_ID&Right_ID) I want to add them with a select statement on the same table.

The values that I want to add to these 3 columns are:

select App_Form_Name,App_Form_Function_ID,Right_ID
from Application_Form_Controls_Management 
where App_Form_Name IN (Select Application_Form_Name From FormNameGridNameTempTable)
group by App_Form_Name,App_Form_Function_ID,Right_ID

This select statement will return values(picking a couple as example):

App_Form_Name      | App_Form_Function_ID    | Right_ID
formContactNewEdit | 1                       | 0
formContactNewEdit | 1                       | 1
formContactNewEdit | 2                       | 1

P.S: This question is not a duplicate of this question because his question was solved by writing a select in each column he wants to insert into, but in my case that would return wrong values because the columns I want to add are "related", so if I do the same that means I will get wrong App_Form_Function_ID&Right_ID values associated to an App_Form_Name column, for example I might see this row added to my table:

App_Form_Name      | App_Function_ID     | Right_ID
formContactNewEdit | 3                   | 1

when the values 3 for App_Function_ID and 1 for Right_ID are wrong for the "formContactNewEdit"

Update,Since someone told me that my question is not clear I will try to ask it in another way:

I have a table Application_Form_Controls_Management in which I want to apply to it an insert into statement.My table has 19 columns, 3 of those columns there values will be inserted with a select statement and the rest manually(by manually I mean I will write by hand their values).

Usually it would be done like this:

Insert Into Application_Form_Controls_Management (column1, App_Form_Name,App_Form_Function_ID,Right_ID,column5, _)
Values('manual value',select statement,select statement,select statement,'the rest are manual values')

But if I do it like that I will get wrong values (explained in the P.S paragraph) so it seems I need to insert those 3 columns with only a single select, but I have no idea how.

Possible duplicate of [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) — Jorge Campos, Aug 19 '19 at 06:39
@JorgeCampos No it is not because in his question it is solved by writing a ```select``` to each column he wants to insert, but in my case the 3 columns are "related" so I can't write for each column a ```select``` because the result will be completely different as it will insert values in the columns: ```App_Form_Function_ID``` and ```Right_ID``` to an ```App_Form_Name``` that aren't supposed to be added to it. — Daniel_Kamel, Aug 19 '19 at 06:44
@mkRabbani I updated my question and asked it in another way — Daniel_Kamel, Aug 19 '19 at 07:09

score 1 · Answer 1 · edited Aug 19 '19 at 07:20

Could this not work?

insert ...
select t1.App_Form_Name, t1.App_Form_Function_ID, t1.Right_ID
from
(
select distinct App_Form_Name,App_Form_Function_ID,Right_ID
from Application_Form_Controls_Management 
where App_Form_Name IN (Select Application_Form_Name From FormNameGridNameTempTable)
) as t1

Note that I used DISTINCT instead of the GROUP for no other reason other than the grouping doesn't really add anything.

score 1 · Accepted Answer · answered Aug 19 '19 at 07:23

Don't use VALUES but SELECT where you add the literal values that you want inserted among the other columns:

insert Into Application_Form_Controls_Management 
(column1, App_Form_Name,App_Form_Function_ID,Right_ID,column5, _)
select 'manual value',App_Form_Name,App_Form_Function_ID,Right_ID,'the rest are manual values'
from Application_Form_Controls_Management 
where App_Form_Name IN (Select Application_Form_Name From FormNameGridNameTempTable)
group by App_Form_Name,App_Form_Function_ID,Right_ID

Insert Into with value select from the same table

2 Answers2