Select query inside Insert Query.

shahnid k

9y5.4k

insert into ACCESS_DETAILS(USER_ID,GROUP_ID,COMPANY_ID) values (1111,11, (select COMPANY_ID from GROUP_COMPANY_CONFIG where GROUP_ID = '1'))

In the above query, the subquery is returning more than one values. so how can i insert it into the table without error.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

ankit srivastava9y ago
1. insert into ACCESS_DETAILS(USER_ID,GROUP_ID,COMPANY_ID) values select 1111 as USER_ID ,11 as GROUP_ID, COMPANY_ID from GROUP_COMPANY_CONFIG where GROUP_ID = '1'
+1
Rafnas T P9y ago
here the problem is select statement is reurning more than one value check in select statement output all are comming same value or not if yes then you can use top 1 to return only one value in select Query as below

insert into ACCESS_DETAILS(USER_ID,GROUP_ID,COMPANY_ID) values (1111,11, (select top 1 COMPANY_ID from GROUP_COMPANY_CONFIG where GROUP_ID = '1'))

if select is returning different values then you have to filter further giving more condition in where clause.
+1
Nitin Sontakke9y ago
It is very simple. The INSERT statement is expecting single value and your SELECT statement is returning multiple values, which is not acceptable.

All you have to do is fix the SELECT statement to return just one value. May be there is an additional WHERE clause which is missing.

Furthermore, from readability point of view you should restructure your query as follows:

insert into ACCESS_DETAILS(USER_ID,GROUP_ID,COMPANY_ID)
select 1111,11, COMPANY_ID
from GROUP_COMPANY_CONFIG where GROUP_ID = '1'
+1
Bryian Tan9y ago
Something like this should work.
;WITH temp AS (
    SELECT 1111 'USER_ID' , 1 'GROUP_ID'
)
INSERT INTO ACCESS_DETAILS(USER_ID,GROUP_ID,COMPANY_ID)
SELECT  t.[USER_ID], t.GROUP_ID, t1.COMPANY_ID
FROM temp t JOIN dbo.GROUP_COMPANY_CONFIG t1 ON t.GROUP_ID = t1.GROUP_ID
+1
Maddy N9y ago
Dear shahnid k,

You can write a cursor to fetch COMPANY_ID from GROUP_COMPANY_CONFIG and insert this into ACCESS_DETAILS table row by row to do this at database level.
for more information about cursor check http://www.sqlservergeeks.com/simple-cursor-in-sql-server-to-insert-records/

Thanks.
+1