November 28, 2007 at 11:48 pm
Hi
I have two tables A, B
In table A i have a column "StartDate" with Date & Time
I want to insert a rows from A to B when system time reaches A.Startdate.
How to do this?
Please provide step by step guidance.
November 29, 2007 at 6:27 am
As far as I know, you're going to have to monitor that table. That means setting up some type of service to check the table periodically and respond to the appropriate time.
You could do this by setting a SQL Agent job that is scheduled to look at the table once a minute and compare the current date & time to the date & time saved there. When the time matches, it can move the data over.
A better approach might be to set up a trigger on the first table that responds to an update of the StartTime (or an insert, whatever) that then modifies the schedule of a SQL Agent job so that it starts at the appropriate time.
The trigger would roughly do something like this (look exact syntax in BOL):
exec msdb.dbo.sp_detach_schedule
exec msdb.dbo.sp_delete_schedule
exec msdb.dbo.sp_add_schedule
exec msdb.dbo.sp_attach_schedule
Use the values from your table and then it'll run when it's needed, no trolling of the table required. This should be far less intrusive than constantly querying the table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply