October 14, 2014 at 6:59 am
I have database that holds column depicting various events and the last time that the event occurred.
I query the database and I get a result like this:
Event Update Time
Event A 2014-10-14 00:35:00.000
Event B 2014-10-14 01:30:00.000
Event C 2014-10-14 00:35:00.000
...
...
Event L 2100-01-01 00:00:00.000
I would like to create another database to hold the various timestamps for each event. Some of these events happen every 2 minutes, others hourly, daily or monthly.
I can query the source every minute. I do not want duplicate entries in the destination when the 'Update Time' has not been updated.
Is it better to query the destination for the latest 'Update Time' value and only INSERT the new record when the time has been updated or just put a UNIQUE constraint on the Event and UpdateTime columns?
October 14, 2014 at 7:27 am
In terms of the how you do this, lots of ways, but the why isn't listed. If you don't want dupes, then can you explain a bit more about what you're doing and why this is an issue? A constraint can prevent this, but it throws errors. Do have have handling to not break the app? Or will users be upset?
If you're trying to avoid dupes, then which ones do you not want? Event A or Event C? In other words, how do you tell which one to remove or keep?
A bit more description would help us provide guidance.
October 14, 2014 at 7:33 am
As STEVE mentioned there are many ways.
Following is a solution as per the understanding i get 🙂
Declare @Source table (EventCol varchar(10), UpdateTime datetime)
Declare @Destination table (EventCol varchar(10), UpdateTime datetime)
-------------- 1st time Data enter
insert into @Source
select 'Event A', '2014-10-14 00:35:00.000' union all
select 'Event B', '2014-10-14 01:30:00.000' union all
select 'Event C', '2014-10-14 00:35:00.000'
MERGE @Destination AS D
USING (
SELECT EventCol, MAX(UpdateTime) AS maxUpdateTime
FROM @SOURCE
GROUP BY EventCol
) AS S(EventCol, maxUpdateTime) ON D.EventCol = S.EventCol
WHEN MATCHED THEN
UPDATE
SET D.UpdateTime = S.maxUpdateTime
WHEN NOT MATCHED THEN
insert (EventCol, UpdateTime)
values ( EventCol, maxUpdateTime);
-------------- 2nd time Data enter (UPDATED TIME)
insert into @Source
select 'Event A', '2014-10-14 01:35:00.000' union all
select 'Event B', '2014-10-14 11:30:00.000' union all
select 'Event C', '2014-10-14 06:35:00.000'
MERGE @Destination AS D
USING (
SELECT EventCol, MAX(UpdateTime) AS maxUpdateTime
FROM @SOURCE
GROUP BY EventCol
) AS S(EventCol, maxUpdateTime) ON D.EventCol = S.EventCol
WHEN MATCHED THEN
UPDATE
SET D.UpdateTime = S.maxUpdateTime
WHEN NOT MATCHED THEN
insert (EventCol, UpdateTime)
values ( EventCol, maxUpdateTime);
-------------- 2nd time Data enter (NEW EVENT)
insert into @Source
select 'Event A', '2014-10-14 01:35:00.000' union all
select 'Event B', '2014-10-14 11:30:00.000' union all
select 'Event C', '2014-10-14 06:35:00.000' union all
select 'Event D', '2014-10-14 06:35:00.000'
MERGE @Destination AS D
USING (
SELECT EventCol, MAX(UpdateTime) AS maxUpdateTime
FROM @SOURCE
GROUP BY EventCol
) AS S(EventCol, maxUpdateTime) ON D.EventCol = S.EventCol
WHEN MATCHED THEN
UPDATE
SET D.UpdateTime = S.maxUpdateTime
WHEN NOT MATCHED THEN
insert (EventCol, UpdateTime)
values ( EventCol, maxUpdateTime);
--- FINAL RESULT
SELECT * FROM @Destination
Hope it helps.
October 14, 2014 at 11:49 am
Steve Jones - SSC Editor (10/14/2014)
In terms of the how you do this, lots of ways, but the why isn't listed.
Thank you, Steve
I would like to save a record of every time an event occurred. I have a database/table (named 'LastTime')that holds a column for a number of events and a column or the last time the event occurred. I would like to create and update a separate table (in a separate database and server) with the history of the events.
At 2:00 pm I query the 'LastTime' table and it returns:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
So, I would want to initially create a table named 'EventHistory' with this info.
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
At 2:01 pm, I query the 'LastTime' table and it returns:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
The Events and UpdateTime combinations are already present in the 'EventHistory' table, so I do not want to add any records.
At 2:02 pm, I query the 'LastTime' table and it returns:
Event UpdateTime
Event A 13:05
Event B 14:02
Event C 12:00
I want to add this row to the 'EventHistory' table, because it doesn't yet exist:
Event B 14:02
The 'EventHistory' table now looks like this:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
Event B 14:02
At 2:03 pm, I query the 'LastTime' table and it returns:
Event UpdateTime
Event A 13:05
Event B 14:02
Event C 12:00
The Events and UpdateTime combinations are already present in the 'EventHistory' table, so I do not want to add any records.
At 2:04 pm, I query the 'LastTime' table and it returns:
Event UpdateTime
Event A 13:05
Event B 14:04
Event C 12:00
I want to add this row to the 'EventHistory' table, because it doesn't yet exist:
Event B 14:04
The 'EventHistory' table now looks like this:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
Event B 14:02
Event B 14:04
At 2:05 pm, I query the 'LastTime' table and it returns:
Event UpdateTime
Event A 14:05
Event B 14:04
Event C 12:00
I want to add this row to the 'EventHistory' table, because it doesn't yet exist:
Event A 14:05
The 'EventHistory' table now looks like this:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
Event B 14:02
Event B 14:04
Event A 14:05
I do this so that I can look back and see when a particular event was updated:
SELECT UpdateTime from EventHistory
WHERE Event = 'Event B'
14:00
14:02
14:04
October 14, 2014 at 12:51 pm
OK, I'm still slightly confused, but I think you mean you have a source where you have everything, all records. However you only want the latest time for an event in your new table?
Is that correct? If so, then I think a merge of data to the new table can work, but it will slow down over time. If you have some way of determining which rows are new in the source, I'd limit those as input to my merge.
If that's correct, then I'm not quite understanding what you mean by the Lasttime table. Remember we can't see your schema or system. What would be helpful if you named things and used those in example, and showed the data flow. The queries you run don't matter. We can change queries to discard or show duplicates or latest times. It's the data that matters here.
October 14, 2014 at 1:54 pm
Steve Jones - SSC Editor (10/14/2014)
OK, I'm still slightly confused, but I think you mean you have a source where you have everything, all records. However you only want the latest time for an event in your new table?
Nope. 🙂 (Thanks for sticking with me, here!)
The latest time for an event in the source table. Basically, I would like to record all of the changes to the source table by creating another table (in another database on another server).
There is a table called 'LastTime'
The table has two columns, 'Event' and 'UpdateTime'
The table has three rows. The values in the Event columns are:
'Event A'
'Event B'
'Event C'
(This column's values will not change.)
The values in the 'UpdateTime' column will be updated at various intervals by a process that I do not control.
In this example:
Event A gets updated every hour at :05 past the hour.
Event B gets updated evey 2 minutes.
Event C gets updated at midnight and noon.
Every time an Event gets updated, the datetime column 'UpdateTime' is updated with the current date and time.
At 2:00 pm, the 'LastTime' table holds these values:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
At 2:01 pm, the 'LastTime' table holds these values:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
At 2:02 pm, the 'LastTime' table holds these values:
Event UpdateTime
Event A 13:05
Event B 14:02
Event C 12:00
At 2:03 pm, the 'LastTime' table holds these values:
Event UpdateTime
Event A 13:05
Event B 14:02
Event C 12:00
At 2:04 pm, the 'LastTime' table holds these values:
Event UpdateTime
Event A 13:05
Event B 14:04
Event C 12:00
At 2:05 pm, the 'LastTime' table holds these values:
Event UpdateTime
Event A 14:05
Event B 14:04
Event C 12:00
I would like to have a record of each time that a row in the 'LastTime' table changes. The 'LastTime' table is in a database that I have read-only access to and on a server that I do not have any permissions to.
So, my plan is to create a new database on "my" server to hold an entry for each time a row in the 'LastTime' table is updated.
I plan to query the 'LastTime' table every minute.
If I intially populate my new table (which I am naming 'EventHistory') at 2:00 pm, it should look like this (the contents of the 'LastTime' table:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
If I query the 'LastTime' table at 2:01 pm, it would not have changed since 2:00.
The 'UpdateTime' of 'Event A' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event A' in the 'EventHistory' table.
The 'UpdateTime' of 'Event B' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event B' in the 'EventHistory' table.
The 'UpdateTime' of 'Event C' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event C' in the 'EventHistory' table.
If I query the 'LastTime' table at 2:02 pm, one row would have changed - the 'Event B' row.
The 'UpdateTime' of 'Event A' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event A' in the 'EventHistory' table.
The 'UpdateTime' of 'Event B' in the 'LastTime' table would be greater than the MAX(UpdateTime) of 'Event B' in the 'EventHistory' table.
The 'UpdateTime' of 'Event C' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event C' in the 'EventHistory' table.
I want to add this row to the 'EventHistory' table:
Event B 14:02
The 'EventHistory' table would then contain:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
Event B 14:02
If I query the 'LastTime' table at 2:03 pm, it would not have changed since 2:02.
The 'UpdateTime' of 'Event A' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event A' in the 'EventHistory' table.
The 'UpdateTime' of 'Event B' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event B' in the 'EventHistory' table.
The 'UpdateTime' of 'Event C' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event C' in the 'EventHistory' table
.
If I query the 'LastTime' table at 2:04 pm, one row would have changed - the 'Event B' row.
The 'UpdateTime' of 'Event A' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event A' in the 'EventHistory' table.
The 'UpdateTime' of 'Event B' in the 'LastTime' table would be greater than the MAX(UpdateTime) of 'Event B' in the 'EventHistory' table.
The 'UpdateTime' of 'Event C' in the 'LastTime' table would not be greate than the MAX(UpdateTime) of 'Event C' in the 'EventHistory' table.
I want to add this row to the 'EventHistory' table:
Event B 14:04
The 'EventHistory' table would then contain:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
Event B 14:02
Event B 14:04
If I query the 'LastTime' table at 2:05 pm, one row would have changed - the 'Event A' row.
The 'UpdateTime' of 'Event A' in the 'LastTime' table would be greater than the MAX(UpdateTime) of 'Event A' in the 'EventHistory' table.
The 'UpdateTime' of 'Event B' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event B' in the 'EventHistory' table.
The 'UpdateTime' of 'Event C' in the 'LastTime' table would not be greater than the MAX(UpdateTime) of 'Event C' in the 'EventHistory' table.
I want to add this row to the 'EventHistory' table:
Event A 14:05
The 'EventHistory' table would then contain:
Event UpdateTime
Event A 13:05
Event B 14:00
Event C 12:00
Event B 14:02
Event B 14:04
Event A 14:05
Thanks for your help!
October 15, 2014 at 1:24 am
inevercheckthis2002 (10/14/2014)
The latest time for an event in the source table. Basically, I would like to record all of the changes to the source table by creating another table (in another database on another server).
Sounds as a trigger specification.
October 15, 2014 at 8:28 am
If that's the case, trigger.
Simple. Make it row based, and you can use Merge, but I'd just do.
create trigger
update LastTime set time = i.time
from inserted
where i.event = lasttime.event
Note that I think LastTime is a horrible name. At least do LastEventTime, or some linkage to the source table. Pre-populate with all events and some time so the trigger can be simple.
If you want, you can add an IF before the update to the trigger that looks for the event and inserts it if it doesn't exist. I might do that in case new events get added and need to flow through.
October 15, 2014 at 1:13 pm
Steve Jones - SSC Editor (10/15/2014)
If that's the case, trigger.Simple. Make it row based, and you can use Merge, but I'd just do.
create trigger
update LastTime set time = i.time
from inserted
where i.event = lasttime.event
Note that I think LastTime is a horrible name. At least do LastEventTime, or some linkage to the source table. Pre-populate with all events and some time so the trigger can be simple.
If you want, you can add an IF before the update to the trigger that looks for the event and inserts it if it doesn't exist. I might do that in case new events get added and need to flow through.
I'll look into this, thank you.
I wonder, though, how I would implement a trigger with a linked server...
Thanks for you help!
October 15, 2014 at 1:45 pm
This is where only allowing updates through stored procedures helps...
it's a simple bit of code added into the stored procedure to write a copy of the event data to a history table locally, or a message queue, then have your process pick those up.
If you do use a trigger, then, again, use it to add rows to a local table or a queue. Pick the rows up from there to send to your linked server using another task, such as a sql agent job. Don't try and send the data to another server from within the trigger.
And don't forget to make your trigger set based - too many times I see triggers written to handle single row updates.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply