Select query inside Insert Query.

Question

insert into ACCESS_DETAILS(USER_IDGROUP_IDCOMPANY_ID) values (111111 ( 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.

ankit srivastava · Answer

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

Rafnas T P · Answer

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_IDGROUP_IDCOMPANY_ID) values (111111 ( 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.

Nitin Sontakke · Answer

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_IDGROUP_IDCOMPANY_ID) select 111111 COMPANY_ID from GROUP_COMPANY_CONFIG where GROUP_ID = '1'

Bryian Tan · Answer

Something like this should work. ; WITH temp AS ( SELECT 1111 'USER_ID'  1 'GROUP_ID' ) INSERT INTO ACCESS_DETAILS(USER_IDGROUP_IDCOMPANY_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

Maddy N · Answer

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.