Insert multiple values in one field SQL with Subquery

Question

i have a a table called messages and a table called customer

I want to insert a new message in the message table for every customer ID in the customers table. I wrote the below query

INSERT INTO MESSAGES
(
ID,
VERSION,
CUSTOMERID,
SERVICE,
CREATED_BY,
MESSAGE
)
VALUES
(
NEWID(),
'1',
SELECT id FROM customer,
null,
'test',
'test')

But i get the error

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

JUST TO ADD that i only need the customer_id from the customer table and no other data — user598347
– user598347, Commented Sep 2, 2014 at 7:29

Joe Taras · Accepted Answer · 2014-09-02 07:37:38Z

2

Not possible, try this:

INSERT INTO MESSAGES
(ID, VERSION, CUSTOMERID, SERVICE, CREATED_BY, MESSAGE)
SELECT NEWID(), '1', id,  null, 'test', 'test'
FROM customer

In this way you can insert in your message table all customer rows

edited Sep 2, 2014 at 7:37

answered Sep 2, 2014 at 7:19

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Taras Over a year ago

Fields' number in INSERT and in SELECT must be the same. In that query by CUSTOMER table you'll get only ID field, the others are constants

Collectives™ on Stack Overflow

Insert multiple values in one field SQL with Subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related