CODEWITHSUNDEEP
sqlinsertsubquery
user598347
user598347

Reputation: 19

Insert multiple values in one field SQL with Subquery

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?

Upvotes: 1

Views: 668

Answers (1)

Joe Taras
Joe Taras

Reputation: 15379

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

Upvotes: 2

Related Questions