SQL Stored Procedure Select Multiple Rows and Insert Into Another Table

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!

Answer

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