Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 796
  • Last Modified:

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?




0
g-spot
Asked:
g-spot
  • 2
  • 2
2 Solutions
 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now