SP Newbie: Select (mult records) and Insert (same mult records)

[AlaskanDad]

Hello all!

I used to be a big contributor in the MS Office section and you all have helped so much in this, the ASP, and HTML/CSS forums that I can't begin to thank everyone.

I'm trying my hand at a complicated (for me, anyway) SP. I've searched through the site and haven't found anything that meets my particular situation so I finally decided to ask you all.

I have an existing SP which is automatically accessed every morning to send out e-mail (to those who have requested it). The SP works just fine and has for years. However, I'd like to add on a UNION ALL Select statement to get even more records and then INSERT INTO the records from the UNION ALL Select statement into a second table. The resulting SP should be both a recordset and an insert action.

I got the UNION ALL combined select statements to work just fine; I got exactly what I was looking for. The first select list gives me 1 for numMsgSchedule and the second list gives me a 0 for numMsgSchedule. Now, I want to take only those records from the resulting combined list with a 0 for numMsgSchedule and insert them into a log table.

Can this be done?

Thanks in advance!

Here is my code (simplified):

ALTER PROCEDURE autoemail_scheduledemails
	@scheddate datetime = null
AS

SET NOCOUNT ON

IF @scheddate IS NULL
BEGIN
	SET @scheddate = GetDate()
END

Select numMsgSchedule, txtMsgTitle, txtMsgSubject, ...otherstuff...
From tblName
Where datMsgDate = @scheddate
UNION ALL
Select numMsgSchedule, txtMsgTitle, txtMsgSubject, ...otherstuff...
From OthertblName
Where datMsgDate = @scheddate

INSERT INTO tblEmailLog 
(txtMsgTitle, txtMsgSubject, ...otherfields...)
Values
(MsgTitleFromAbove, txtMsgSubjectFromAbove, ...othervalues from above...)
Where numMsgScheduleFromAbove = 0

SET NOCOUNT OFF

 

[r937]

if the second SELECT in the UNION gives the 0, and all you want is the 0, then why do you need the UNION?

r937.com | rudy.ca

 

[AlaskanDad]

Thanks for the follow up question.

The UNION brings together two lists: individual e-mail and category e-mail. I want to send out both.

The INSERT is to only record the category e-mail (the ones with the 0).

 

[AlaskanDad]

FOUND THE ANSWER! (and it was much easier than I thought)

ALTER PROCEDURE autoemail_scheduledemails
    @scheddate datetime = null
AS

SET NOCOUNT ON

IF @scheddate IS NULL
BEGIN
    SET @scheddate = GetDate()
END

Select numMsgSchedule, txtMsgTitle, txtMsgSubject, ...otherstuff...
From tblName
Where datMsgDate = @scheddate
UNION ALL
Select numMsgSchedule, txtMsgTitle, txtMsgSubject, ...otherstuff...
From OthertblName
Where datMsgDate = @scheddate

INSERT INTO tblEmailLog
(txtMsgTitle, txtMsgSubject, ...otherfields...)
Select MsgTitleFromAbove, txtMsgSubjectFromAbove, ...othervalues from above...
From OthertblName
Where datMsgDate = @scheddate
and numMsgSchedule = 0

SET NOCOUNT OFF

I just learned that to insert multiple rows, you don't use the "Values" keyword but, instead, use a SELECT statement.

Please correct me if I'm wrong.