jaymz69
asked on
Having a Select Update to Table for the Next Time
I have a query that runs every hour during business in VFP.
As I am converting it to SQL Server 2008 r2 I have come accross a differeence in how to handle this:
What It does is quries the data and if it meets the conditions I would update a DBF (table1)
Then SELECT the current query and compared to the last hour (table2)
If there are no matching records then that must be a new order written since the last hour
and I want it to go into the SSRS in an Emaiil...
What is the best apporch on this?
SELECT *
FROM table_orders
WHERE............
-- Update table2 ? (a table I created to store and append new data only?
SELECT *
FROM table2 t2 INNER JOIN ON table3 t3
t2.order != t2.orders
-- Save new data as table 3 for the next time the query runs?
As I am converting it to SQL Server 2008 r2 I have come accross a differeence in how to handle this:
What It does is quries the data and if it meets the conditions I would update a DBF (table1)
Then SELECT the current query and compared to the last hour (table2)
If there are no matching records then that must be a new order written since the last hour
and I want it to go into the SSRS in an Emaiil...
What is the best apporch on this?
SELECT *
FROM table_orders
WHERE............
-- Update table2 ? (a table I created to store and append new data only?
SELECT *
FROM table2 t2 INNER JOIN ON table3 t3
t2.order != t2.orders
-- Save new data as table 3 for the next time the query runs?
ASKER
There is a timeStamp in the File for orders
ASKER
I can see that but being new to SQL how would I store the last time run?
Thanks
Thanks
Don't keep on creating tables. It would be complex to maintain. Create one table and insert/update data based on a datetime column. Can you explain with some sample data?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
table2 would be one I would create once then use insert/update data
ASKER
location order# Name
xx 123456 joeUser
yy 5351 janeUser
xx 123456 joeUser
yy 5351 janeUser
Why do you want to create table 2, 3 and so on?
ASKER
That is just how it was purposed by some else.
I used only 1 dbf in VFP when it runs on it.
Now I need to convert over to SQL Server 2008 so I am looking for best practice on
this scenario.
The actual File in the AS400 for Orders does have a field for date/time taken
I can rune my SELECT then the WHERE would have to comapre the Last hour some how...?
I used only 1 dbf in VFP when it runs on it.
Now I need to convert over to SQL Server 2008 so I am looking for best practice on
this scenario.
The actual File in the AS400 for Orders does have a field for date/time taken
I can rune my SELECT then the WHERE would have to comapre the Last hour some how...?
ASKER
SELECT fldTime
FROM table1
-- This is where I do not understand the time stamp yet
-- I can get the time the order was written and sysdatetime -1 (for the last hour)?
WHERE fldTime < SYSDATETIME
FROM table1
-- This is where I do not understand the time stamp yet
-- I can get the time the order was written and sysdatetime -1 (for the last hour)?
WHERE fldTime < SYSDATETIME
If Exists (Select * FROM Table2)
Insert Into Table2
Insert Into Table2
ASKER
I think it needs the date in the OPEnQUERY but as a string
When I plug in the OPENQURY
date=' '2011-05-11' '
it works and quickly like it should
When I plug in the OPENQURY
date=' '2011-05-11' '
it works and quickly like it should
Anyway, it sounds like you want a query where records from the first SELECT are NOT IN the records of the second SELECT:
http://msdn.microsoft.com/en-us/library/ms177682.aspx