move record from TableA to TableB at a specified time of the record

How can i move record from TableA to TableB at a specified time of the
record?
Thanks for any hints.
TonyTony WONG wrote:
> How can i move record from TableA to TableB at a specified time of the
> record?
> Thanks for any hints.
> Tony
One obvious way might be like this:
CREATE VIEW TableB
AS
SELECT col1, col2, col3
FROM TableA
WHERE datetime_col <= CURRENT_TIMESTAMP
GO
If you want to archive data on a regular bases with INSERT statements
then create a proc and schedule it as a job.
In SQL Server 2005 you may want to consider using a partitioned table
instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for your prompt advice
may i know the best way to trigger this sql?
add a job to execute in every 5 minutes?
thanks a lot.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1147069404.714636.117510@.y43g2000cwc.googlegroups.com...
> Tony WONG wrote:
> One obvious way might be like this:
> CREATE VIEW TableB
> AS
> SELECT col1, col2, col3
> FROM TableA
> WHERE datetime_col <= CURRENT_TIMESTAMP
> GO
> If you want to archive data on a regular bases with INSERT statements
> then create a proc and schedule it as a job.
> In SQL Server 2005 you may want to consider using a partitioned table
> instead.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Tony WONG wrote:
> Thanks for your prompt advice
> may i know the best way to trigger this sql?
> add a job to execute in every 5 minutes?
>
For what purpose would you want to do this every 5 minutes? That seems
much too frequent for a simple archiving process. I'd say there ought
to be a better solution - maybe replication, mirroring or triggers for
example - but that depends on just why you want two tables to replicate
the same data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tony WONG wrote:
> Thanks for your prompt advice
> may i know the best way to trigger this sql?
> add a job to execute in every 5 minutes?
>
For what purpose would you want to do this every 5 minutes? That seems
much too frequent for a simple archiving process. I'd say there ought
to be a better solution - maybe replication, mirroring or triggers for
example - but that depends on just why you want two tables to replicate
the same data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||in fact it is a SMS system
TableA store the record to be sent to TableB(production table)
Once records add to TableB, the record will be distributed to SMS queue.
The question is what is the best method to do it (add a job to do every 5
minutes) in terms of hardware resource (cpu loading...)
by the way, how to add a job in every 5 minutes?
Thanks a lot for your assistance.
tony
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1147109579.095918.27650@.g10g2000cwb.googlegroups.com...
> Tony WONG wrote:
> For what purpose would you want to do this every 5 minutes? That seems
> much too frequent for a simple archiving process. I'd say there ought
> to be a better solution - maybe replication, mirroring or triggers for
> example - but that depends on just why you want two tables to replicate
> the same data.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Tony WONG wrote:
> in fact it is a SMS system
> TableA store the record to be sent to TableB(production table)
> Once records add to TableB, the record will be distributed to SMS queue.
> The question is what is the best method to do it (add a job to do every 5
> minutes) in terms of hardware resource (cpu loading...)
> by the way, how to add a job in every 5 minutes?
Doesn't make much sense to me as you've described it. If the date is
what determines when a row is to be processed then why do you need two
tables? You could have just one table and create a view as I first
suggested.
Two similar tables in the same database is usually considered to be a
logical design flaw. It is also inefficient if it forces you to insert
the same data twice, especially if you have to do so every 5 minutes.
Consider changing the design.
If you have to work with what you have then you could either implement
a trigger or create a job (lookup sp_add_job in Books Online). If the
inserts are always to be time driven then use a job.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--