Iterate through table or use SQL query

I have a number of tables that are basically log files of emails that have been sent to customers.

I need to work through each table and for each record update a master table that will contain summary details of whether the email was sent or not.

So as it works through each log:

1. If the email doesnt exist in the master table then add to the master table. If the email was sent add a '1' in the 'Sent' column and a '0' in the 'Fail' column, also add the log name in the 'LogID' column. Conversely if the email failed do the opposite.

2. If the email does exit in the master table then update that record. If the email was sent then add 1 to the value that already exists in the 'Sent' column and just make the 'Fail' column value '0'. If the email failed then make the 'Sent' column value '0' and add 1 to the value in the 'Fail' column.

This seems to me to be an iterative process that can't be performed with a single query. Am I right?




g-spotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MakolyteCommented:
Case 1 you'll be inserting new records. Case 2 you'll be updating existing records.

Case 1: INSERT .... WHERE emailID not in (SELECT emailID FROM masterTable)

Case 2: UPDATE ... WHERE emailID in (SELECT emailID FROM masterTable)

These do not require iterative approaches, but you'll definately need to do an INSERT and an UPDATE
0
Mark WillsTopic AdvisorCommented:
But do the UPDATE first otherwise you end up udating all those that have just been added...

The only iterative part is to loop through each of the log tables - and you should be able to get a list from information_schema.tables so long as the follow a naming convention. In which case you would be inclined to use dynamic sql so you can manipulate / set the "from" datasource to be a variable retrieved from the list of table names.

Make sense ?

0
g-spotAuthor Commented:
Hi Mark and Makolyte

Thanks for your help. With regard to the log tables I can get each log table name by doing a select on my BroadcastsLog table:

SELECT LogID FROM BroadcastsLog WHERE Processed = 0

This might give:
112
113
114

All log tables have a prefix "log_"

So using Dynamic SQL I have to do something like

For each LogID...
UPDATE MasterEmailTable SET... FROM log_112
INSERT MasterEmailTable ... FROM log_112
NEXT

How do I perform the "For each" part in SQL?

0
Mark WillsTopic AdvisorCommented:

something like :


declare @sql varchar(max)
declare @logid int
set @logid = (select max(logid) from broadcastslog where processed = 0)
 
while @logid > 0
begin
 
    set @sql = 'UPDATE MasterEmailTable SET column = LG.column FROM log_' +convert(varchar,@logid) + ' LG where conditions_go_here and exists (select NULL from MasterEmailTable MET where MET.PK = LG.PK )'
    --exec (@sql)
    print @sql
 
    set @sql = 'INSERT MasterEmailTable (column_definitions) SELECT LG.col1,LG.col2 FROM log_' +convert(varchar,@logid) + ' LG where conditions_go_here and not exists (select NULL from MasterEmailTable MET where MET.PK = LG.PK )'
    --exec (@sql)
    print @sql
 
    update broadcastslog set processed = 1 where logid = @logid
 
    set @logid = isnull((select max(logid) from broadcastslog where processed = 0),0)
 
end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
g-spotAuthor Commented:
Hi there

Thanks for your help Mark. I'm inclined to offer you the majority of the points here as your advice really guided me as to what to do.

For the benefit of anyone reading this, my finished stored procedure is below:

Thanks again.


CREATE PROCEDURE [dbo].[UpdateEmailLog]
AS
 
DECLARE @sql nvarchar(MAX)
DECLARE @logId int
DECLARE @logIdString varchar(20)
DECLARE @logTable varchar(20)
 
SET @logid = (SELECT MAX(ID) FROM BroadcastLog WHERE Processed = 0 AND EntryDate < DATEADD(d, -3, GETDATE()))
 
WHILE @logid > 0
 
BEGIN
 
SET @logIdString = CONVERT(varchar(10),@logid)
SET @logTable = 'log_' + REPLICATE('0', 8 - LEN(@logid)) + @logIdString
 
SELECT @sql = 'UPDATE EmailLog
			SET Sent = EmailLog.Sent + 1, Failed = 0, LastBroadcast = ' + @logIdString + ', LastSendDate = ' + @logTable + '.EntryDate, LastFailCode = ' + @logTable + '.FailCode, LastFailMessage = ' + @logTable + '.Comment
			FROM EmailLog INNER JOIN ' + @logTable + ' ON EmailLog.EmailAddress = ' + @logTable + '.EmailAddress
			WHERE ' + @logTable + '.Sent = 1'
EXEC sp_executesql @sql
 
SELECT @sql = 'UPDATE EmailLog
			SET Sent = 0, Failed = EmailLog.Failed + 1, LastBroadcast = ' + @logIdString + ', LastSendDate = ' + @logTable + '.EntryDate, LastFailCode = ' + @logTable + '.FailCode, LastFailMessage = ' + @logTable + '.Comment
			FROM EmailLog INNER JOIN ' + @logTable + ' ON EmailLog.EmailAddress = ' + @logTable + '.EmailAddress
			WHERE ' + @logTable + '.Sent = 0'
EXEC sp_executesql @sql
 
SELECT @sql = 'INSERT INTO EmailLog
			(EmailAddress, Sent, Failed, LastBroadcast, LastSendDate, LastFailCode, LastFailMessage)
			SELECT EmailAddress, 1 AS Sent, 0 AS Failed, ' + @logIdString + ' AS LastBroadcast, EntryDate, FailCode, Comment
			FROM ' + @logTable +
			' WHERE (Sent = 1) AND NOT EXISTS (SELECT * FROM EmailLog WHERE EmailAddress = ' + @logTable + '.EmailAddress)'
EXEC sp_executesql @sql
 
SELECT @sql = 'INSERT INTO EmailLog
			(EmailAddress, Sent, Failed, LastBroadcast, LastSendDate, LastFailCode, LastFailMessage)
			SELECT EmailAddress, 0 AS Sent, 1 AS Failed, ' + @logIdString + ' AS LastBroadcast, EntryDate, FailCode, Comment
			FROM ' + @logTable +
			' WHERE (Sent = 0) AND NOT EXISTS (SELECT * FROM EmailLog WHERE EmailAddress = ' + @logTable + '.EmailAddress)'
EXEC sp_executesql @sql
 
UPDATE BroadcastLog set Processed = 1 where ID = @logid
 
SET @logid = (SELECT MAX(ID) FROM BroadcastLog WHERE Processed = 0 AND EntryDate < DATEADD(d, -3, GETDATE()))
 
SET @logid = ISNULL(@logid,0)
 
END

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.