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: 205
  • Last Modified:

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?

0
jaymz69
Asked:
jaymz69
  • 7
  • 2
  • 2
  • +1
1 Solution
 
Paul MacDonaldDirector, Information SystemsCommented:
Is there a date/time stamp field?  It seems like it would be easier to just query the database where the timestamp is greater than the last run time.

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
0
 
jaymz69Author Commented:
There is a timeStamp in the File for orders

0
 
jaymz69Author Commented:
I can see that but being new to SQL how would I store the last time run?

Thanks
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
SharathData EngineerCommented:
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?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
"I store the last time run?"
Put it in another table?

 or do the NOT IN query:
SELECT *
FROM table
WHERE Order NOT IN
  (SELECT Order
  FROM table2)
0
 
jaymz69Author Commented:
table2 would be one I would create once then use insert/update data


0
 
jaymz69Author Commented:
location    order#    Name    
xx             123456   joeUser
yy              5351      janeUser



0
 
SharathData EngineerCommented:
Why do you want to create table 2, 3 and so on?
0
 
jaymz69Author Commented:
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...?
0
 
jaymz69Author Commented:
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
0
 
Alpesh PatelAssistant ConsultantCommented:
If Exists (Select * FROM Table2)
Insert Into Table2
0
 
jaymz69Author Commented:
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
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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