SQL Stored Procedure Select Multiple Rows and Insert Into Another Table

Question

I know this question may sound like a duplicate, but I searched and couldn't find a good answer yet.

I have a stored procedure (started) that looks like this:

Declare @idCC int
Declare @TrStatus int
Declare @FirstName varchar(50)
Declare @LastName varchar(50)
Declare @Email varchar(50)
Declare @Plant varchar(50)


SELECT @idCC=dbo.CC_Contacts.idCC, @FirstName=dbo.CC_Contacts.Firstname, @LastName=dbo.CC_Contacts.Lastname, @Email=dbo.CC_Contacts.Email, @Plant=dbo.CC_Plants.PlantName
FROM         dbo.CC_Contacts INNER JOIN
                      dbo.CC_ContactGroups ON dbo.CC_Contacts.idCC = dbo.CC_ContactGroups.idCC INNER JOIN
                      dbo.CC_Plants ON dbo.CC_Contacts.idPlant = dbo.CC_Plants.idPlant CROSS JOIN
                      dbo.CC_Groups INNER JOIN
                      dbo.CC_ECPGroups ON dbo.CC_Groups.idGroup = dbo.CC_ECPGroups.idGroup INNER JOIN
                      dbo.ECP_ProbRpt ON dbo.CC_ECPGroups.ECPName = dbo.ECP_ProbRpt.ECPName
WHERE (dbo.ECP_ProbRpt.ProbIdx = @PRid) AND (dbo.CC_Groups.IsPRGroup = 1) AND (dbo.CC_ECPGroups.Condition = 1) AND (dbo.CC_Contacts.Status = 1) AND 
                      (dbo.CC_Contacts.PRnotify = 1)

INSERT INTO [dbo].[ECP_TR_Recipients](idTr, TrStatus, idCC, FirstName, LastName, Email, Plant)                     
VALUES ( @idTr, '1', @idCC, @FirstName, @LastName, @Email, @Plant)

The problem is the Select returns multiple rows and all of them need to be inserted into the other table. I'm really just not sure where to go from here. Any ideas or suggestions would be appreciated.

I tested the select query separately, it works great. I just need it to iterate through the results and insert them all.

Thanks for your help!

BTW: The @idTR is defined elsewhere in the Stored Procedure. — PigsIncorporated
– PigsIncorporated, Commented Mar 8, 2016 at 17:34
Instead the INSERT INTO <table>(<cols>) VALUES(<vals>), write INSERT INTO <table>(<cols>) SELECT <vals> FROM <the rest of your query>. Quick reference — TT.
– TT., Commented Mar 8, 2016 at 17:43

believe me · Accepted Answer · 2016-03-08 18:06:15Z

2

You don't need to use a declaration just use insert into table1 column_name select column_name from table2, in your example like this

INSERT INTO [dbo].[ECP_TR_Recipients](idTr, TrStatus, idCC, FirstName, LastName, Email, Plant)
SELECT dbo.CC_Contacts.idCC, dbo.CC_Contacts.Firstname, dbo.CC_Contacts.Lastname, dbo.CC_Contacts.Email, dbo.CC_Plants.PlantName
FROM         dbo.CC_Contacts INNER JOIN
                      dbo.CC_ContactGroups ON dbo.CC_Contacts.idCC = dbo.CC_ContactGroups.idCC INNER JOIN
                      dbo.CC_Plants ON dbo.CC_Contacts.idPlant = dbo.CC_Plants.idPlant CROSS JOIN
                      dbo.CC_Groups INNER JOIN
                      dbo.CC_ECPGroups ON dbo.CC_Groups.idGroup = dbo.CC_ECPGroups.idGroup INNER JOIN
                      dbo.ECP_ProbRpt ON dbo.CC_ECPGroups.ECPName = dbo.ECP_ProbRpt.ECPName
WHERE (dbo.ECP_ProbRpt.ProbIdx = @PRid) AND (dbo.CC_Groups.IsPRGroup = 1) AND (dbo.CC_ECPGroups.Condition = 1) AND (dbo.CC_Contacts.Status = 1) AND 
                      (dbo.CC_Contacts.PRnotify = 1)

If you want to add more static columns in the table just add fake columns in the select statement

INSERT INTO [dbo].[ECP_TR_Recipients](idTr, TrStatus, idCC, FirstName, LastName, Email, Plant)
    SELECT @idTr as idTr, '1', dbo.CC_Contacts.idCC, dbo.CC_Contacts.Firstname, dbo.CC_Contacts.Lastname, dbo.CC_Contacts.Email, dbo.CC_Plants.PlantName

edited Mar 8, 2016 at 18:06

answered Mar 8, 2016 at 17:44

believe me

9081 gold badge5 silver badges24 bronze badges

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

7 Comments

PigsIncorporated Over a year ago

The only issue is there is two columns on the insert that are not derived from the select. How do you add values for insertion?

believe me Over a year ago

Are those two columns static values?

PigsIncorporated Over a year ago

One is a static value, always will be '1' and the other is @idTr inserted into the stored procedure but always static for the duration of the command.

PigsIncorporated Over a year ago

The insert wasn't inserting any data until I took what you provided and added Select @idTr AS idTr, '1' as TrStatus,...

believe me Over a year ago

@PigsIncorporated yeah, that is because a declaration variable cannot be used as a column I updated my answer accordingly

|

Collectives™ on Stack Overflow

SQL Stored Procedure Select Multiple Rows and Insert Into Another Table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related