CODEWITHSUNDEEP
sql
k80sg
k80sg

Reputation: 2473

SQL Server + Inserting values from a select statement into a table

is it possible to do this:

SELECT UserName FROM payroll p
INNER JOIN aspnet_Users u
ON (p.staffId= u.UserId)
WHERE jobId = '1011'

For example if the above select returns say 3 results: john001, mary002, peter003

How can I use it to insert the individual results into my table(Notification) columns? In this case, only the [Recipient] column will have different values and will be the results return from the above Select statement.

INSERT INTO [Notification]
(
[Recipient], 
[Message],
[Type],
[DateCreated]
)
VALUES
(
-- The results from the select statement
'Testing Message'
'Type1,
GETUTCDATE()
)

So end up I should have these columns in my Notification table:

john001, Testing Message, Type1, 10/10/1945
mary002, Testing Message, Type1, 10/10/1945
peter003, Testing Message, Type1, 10/10/1945

Thanks in advance.

Upvotes: 1

Views: 2448

Answers (3)

Taryn
Taryn

Reputation: 247620

You will want to use an INSERT INTO..SELECT..FROM query:

INSERT INTO [Notification]
(
    [Recipient], 
    [Message],
    [Type],
    [DateCreated]
)
SELECT UserName, 'Testing Message', 'Type1', GETUTCDATE()
FROM payroll p
INNER JOIN aspnet_Users u
    ON (p.staffId= u.UserId)
WHERE jobId = '1011'

Reference Material:

Upvotes: 2

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

INSERT INTO Notification
(
Recipient, 
Message,
Type,
DateCreated
)
SELECT UserName, 'Testing Messag', 'Type1', GETUTCDATE()
 FROM payroll p
 JOIN aspnet_Users u
  ON (p.staffId= u.UserId)
Where jobId = '1011'

Upvotes: 1

Eric J.
Eric J.

Reputation: 150108

You could do

INSERT INTO [Notification]
(
[Recipient], 
[Message],
[Type],
[DateCreated]
)
SELECT UserName, 
'Testing Message' 
'Type1,
GETUTCDATE()
FROM payroll p
INNER JOIN aspnet_Users u
ON (p.staffId= u.UserId)
WHERE jobId = '1011'

You can use the INSERT and SELECT statements to add rows to a table in the following ways:

Use the INSERT statement to specify values directly or from a subquery.

Use the SELECT statement with the INTO clause.

http://msdn.microsoft.com/en-us/library/ms188263(v=sql.105).aspx

Additional reference:

http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Upvotes: 1

Related Questions