Anyone know how to do this in less lines of code?

Question

Post reply

Anyone know how to do this in less lines of code?

Gloriann

Ten Centuries

Points: 1192
More actions
April 25, 2006 at 12:40 pm

#172181

Anyone know how to do this in less lines of code?
INSERT INTO @tempEmail (email_address, DisplayName, P.ProjectID, OrganizationID, type)
SELECT DISTINCT 'name1@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3
FROM ReportOpenItemsOverDue H
WHERE H.InsuranceID = 26
INSERT INTO @tempEmail (email_address, DisplayName, P.ProjectID, OrganizationID, type)
SELECT DISTINCT 'name2@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3
FROM ReportOpenItemsOverDue H
WHERE H.InsuranceID = 26
INSERT INTO @tempEmail (email_address, DisplayName, P.ProjectID, OrganizationID, type)
SELECT DISTINCT 'name3@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3
FROM ReportOpenItemsOverDue H
WHERE H.InsuranceID = 26
INSERT INTO @tempEmail (email_address, DisplayName, P.ProjectID, OrganizationID, type)
SELECT DISTINCT 'name4@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3
FROM ReportOpenItemsOverDue H
WHERE H.InsuranceID = 26
Just seems so redundant, thoughts?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

From where are you getting the e-mails?

At minimum, you could do something like this perhaps:

Declare @email varchar(75)

set @email = 'email1'

INSERT INTO @tempEmail (email_address, DisplayName, P.ProjectID, OrganizationID, type)

SELECT DISTINCT @email,'Insurance Company', H.ProjectID, H.OrganizationID, 3

FROM ReportOpenItemsOverDue H

WHERE H.InsuranceID = 26

Or perhaps a table-type approach if it's a set list of values.

Mark Gorelik Old Hand Points: 396 More actions · Answer 2

can't this be done by using union all

INSERT INTO @tempEmail (email_address, DisplayName, P.ProjectID, OrganizationID, type)

SELECT DISTINCT 'name1@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3

FROM ReportOpenItemsOverDue H

WHERE H.InsuranceID = 26

UNION ALL

SELECT DISTINCT 'name2@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3

FROM ReportOpenItemsOverDue H

WHERE H.InsuranceID = 26

UNION ALL

SELECT DISTINCT 'name3@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3

FROM ReportOpenItemsOverDue H

WHERE H.InsuranceID = 26

UNION ALL

SELECT DISTINCT 'name4@company.com','Insurance Company', H.ProjectID, H.OrganizationID, 3

FROM ReportOpenItemsOverDue H

WHERE H.InsuranceID = 26

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

I assume your concern is running 4 separate queries on the table ReportOpenItemsOverDue.

How about creating a table of the email addresses, then doing a cross-join on the email address table and the ReportOpenItemsOverDue.

Create table #MyEmailAddr (email varchar(100))

Insert into #MyEmailAddr values ('name1@company.com')
Insert into #MyEmailAddr values ('name2@company.com')
Insert into #MyEmailAddr values ('name3@company.com')
Insert into #MyEmailAddr values ('name4@company.com')

INSERT INTO @tempEmail (email_address, DisplayName, P.ProjectID, OrganizationID, type)
SELECT m.email, 'Insurance Company', H.ProjectID, H.OrganizationID, 3
FROM #MyEmailAddr m CROSS JOIN 
(SELECT DISTINCT ProjectID, OrganizationID 
 FROM ReportOpenItemsOverDue 
 WHERE InsuranceID=26) h

In this way, you query the ReportOpenItemsOverDue table only once, do the distinct and where clause only once on that table, then create separate records for each email. And if the emails are constant for each time your run this, create the table as a permanent table and it is that much easier.

Hope this helps.

Mark