CODEWITHSUNDEEP
sqlsql-server
user3746002
user3746002

Reputation: 27

SQL Server : stored procedure add table content to another table

How can I add the results from a query by parameter to another table using a stored procedure?

Here is my select query text:

SELECT        
    dbo.PortfolioH.Epic, 
    dbo.PortfolioH.AlertRatings,
    dbo.UserAccounts.Email, 
    dbo.PortfolioH.UserN
FROM            
    dbo.PortfolioH 
LEFT OUTER JOIN
    dbo.UserAccounts ON dbo.PortfolioH.UserN = dbo.UserAccounts.UserN
GROUP BY 
    dbo.PortfolioH.Epic, 
    dbo.PortfolioH.AlertRatings, 
    dbo.UserAccounts.Email,
    dbo.PortfolioH.UserN
HAVING        
    (dbo.PortfolioH.AlertRatings = N'yes')

I want to append to table AlertEmails where dbo.PortfolioH.Epic = @Epic

any help greatly appreciated.

Upvotes: 0

Views: 73

Answers (1)

Alejandro
Alejandro

Reputation: 7813

Just use the standard INSERT.....SELECT construct, which takes the data to be inserted from a query, something like this:

INSERT INTO dbo.AlertsEmails (Epic, AlertRatings, Email, UserN, AlertType)

SELECT        dbo.PortfolioH.Epic, dbo.PortfolioH.AlertRatings,
dbo.UserAccounts.Email, dbo.PortfolioH.UserN, 1 As AlertType

FROM            dbo.PortfolioH LEFT OUTER JOIN
dbo.UserAccounts ON dbo.PortfolioH.UserN = dbo.UserAccounts.UserN

GROUP BY dbo.PortfolioH.Epic, dbo.PortfolioH.AlertRatings, dbo.UserAccounts.Email,
dbo.PortfolioH.UserN
HAVING        (dbo.PortfolioH.AlertRatings = N'yes') ;

It just runs the query, but instead of returning a result set, inserts the resulting rows into the table. Note that I've added the AlertType column in the INSERT part and added a fixed value in the SELECT part, as they must match exactly.

Upvotes: 3

Related Questions