Automating SQL queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Automating SQL queries

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2010-12-08 : 05:54:11

At the moment I have a SQL database that I use for email marketing purposes to generate new sales leads. The database has 1.5 million records with various fields with the most important being: uniqueid, website, expressemail, salutation, lastmailed, batch

Everyday I run email marketing campaigns based on a specific batch number. I use this view to select my data to email.

select uniqueid, website, expressemail, salutation, lastmailed, batch from main_table

WHERE (ExpressEmail LIKE '%@%')AND (NOT (WebSite IS NULL OR WebSite = '')) AND (Batch IN ('269')) AND (Salutation LIKE 'there')

As you can see it is a really simple query and I have to change the batch number everytime I want to send an email. I was wondering if there was any way of automating the selection of batch numbers based on a set of criteria?

JT

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 05:59:24

So you want help with a system generating spam :).

Yes - what sort of criteria are you loking for.
You will have to change the criteria anyway so will that be easier than changing the batch number?
You can populate a temp table with batch numbers and use that in the in clause.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Topaz
Posting Yak Master

199 Posts

Posted - 2010-12-08 : 06:26:44

quote:
Originally posted by nigelrivett

So you want help with a system generating spam :)

It's not spam, just a system to send out solicited emails ;)

quote:
Yes - what sort of criteria are you loking for.
You will have to change the criteria anyway so will that be easier than changing the batch number?
You can populate a temp table with batch numbers and use that in the in clause.

In answer to the above. The only criteria that changes would be the batch number.

I'm intrigued with populating a temp table. I've never done that before but thanks for showing your willingness to help me.

JT

Topaz
Posting Yak Master

199 Posts

Posted - 2010-12-08 : 06:31:49

The extra criteria that I'm talking about is a little bit complex to explain. But I'll this is the simplest example:

Each batch number consists of around 3500 records.
Each batch number is only allowed to be emailed once in 42 days.

JT

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 06:37:33

Well this will create a temp table with the data

select uniqueid, website, expressemail, salutation, lastmailed, batch from main_table
into #mytable
WHERE (ExpressEmail LIKE '%@%')AND (NOT (WebSite IS NULL OR WebSite = '')) AND (Batch IN ('269')) AND (Salutation LIKE 'there')

If you want to get all the batch numbers that haven't been emailed in the last 42 days and have a table
BatchEmail(batch, dte)

select distinct batch
into #batches
from BatchEmail
group by batch
having max(dte) < getdate() - 43

Note - you would have to left join to a batch table to get new batches.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources