how to insert data with subquery that returns more than on value?

Question

I have two tables company_list and company_details. The company_list table has a company_id column, and the company_details has some information related to the company with the company_id. I wanted to insert data into the company_details table with the company_id not in the company_details table. I tried it with the following query but it gave me an error.

INSERT INTO company_details (company_id,detail_found)
VALUES ((SELECT company_id FROM company_list WHERE company_id  NOT IN (SELECT company_id FROM company_details)), 'No data found');

The error is:

Msg 512, Level 16, State 1, Line 27

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

How to insert data into the table with a subquery that returns more than one value?

use a simple `INSERT INTO . . . SELECT ... FROM` query – Squirrel Jan 09 '23 at 08:42 — Squirrel, Jan 09 '23 at 08:42

score 2 · Answer 1 · answered Jan 09 '23 at 08:43

You can use an insert-select statement (without the values clause), and query the string literal as one of the select's columns:

INSERT INTO company_details (company_id,detail_found)
SELECT company_id, 'No data found' 
FROM   company_list 
WHERE  company_id NOT IN (SELECT company_id FROM company_details)

how to insert data with subquery that returns more than on value?

1 Answers1