Insert multiple records into a table based on Id's from another

Transact-SQL
1

HI all,

I have a message table that acts like internal email; a user can send another user a message. I have a case where I want to send all users the same message, so say I have 10 users, I want to insert 10 messages into the message table identical except for the 'To' field, which should be each users unique 'Id'.

So for a message to a single recipient I would have:

INSERT INTO Messages To=@To, [From]=@From, Subject=@Subject, Message=@Message

I can't even get started on this, I know I need to SELECT Id FROM Users, then use this in the original query...

something like:

INSERT INTO Messages To=( SELECT Id FROM Users ), [From]=@From, Subject=@Subject, Message=@Message

2

You are close

INSERT INTO Messages (To, From, Subject, Message)
SELECT ID, @From, @Subject, @Message
FROM Users
3

Yep - that's it!
thanks

now suppose I had a list instead of a table:

INSERT INTO Messages (To, From, Subject, Message)
SELECT ID, @From, @Subject, @Message
WHERE ID IN (10,11,12)

gives me an error :{

4

Go it!

INSERT INTO Messages (To, From, Subject, Message)
SELECT ID, @From, @Subject, @Message
FROM (VALUES (10), (11)) AS x(ID)
1 Like