?
Solved

What is the recommended way to perform an automated daily SQL update query?

Posted on 2016-07-31
7
Medium Priority
?
98 Views
Last Modified: 2016-08-01
Hi Experts,

I need to perform a SQL update query in a daily bases (something like 'update MyTable set MyField = False where..'), wondering what is the best way recommended for performing such a task?
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 41736936
you can create a SQL job to execute the query you want.

Create a Transact-SQL Job Step
https://msdn.microsoft.com/en-sg/library/ms187910.aspx

Create a Schedule
https://msdn.microsoft.com/en-sg/library/cc645912.aspx
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41737568
Ryan has given you the answer you asked for but the fact that you need to do this indicates that your schema is not normalized.  You might want to consider calculating the true/false in a query or view so it is always up to date.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41738098
@Pat,
This is a case where the 90% is being governed by rules (false), however users want reserve the right to overwrite rules, and this is why I need to store them in DB.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41738100
Thank you.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41738221
Storing an override does not necessitate running a bulk update.  For example, in an Order Entry application, the default price is stored in the product record.  Then when you add a product to an order creating a row in the Order Details table, you copy the unit price from the product record and place it in the order details record.  Then the user can change it if necessary.  Whenever you look at the order, you see the price charged for an item rather than the suggested price.  But you do NOT store the sum of all the order details price * quantity calculations in the Order header table since that violates normaliztion rules whereas copying the suggested price does not and the reason is that the suggested price is the price at a point in time rather than the current price and may in fact be different from any standard price charged in history.

What you are doing sounds something like storing someone's age.   You would have to recalculate the age every day otherwise some part of the population will have an incorrect age in years.  This is absolutely wrong and would never be done except perhaps in a data warehouse application where you only updated the data weekly or monthly and even then storing age is questionable unless you always want the age as of the date the warehouse was last updated.

Anyway, you have your answer so that is all I have to say.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41738270
Hi,

To be specific, this is an Employees table and users wants to see if all info including health docs are up to date.

Now I was only given certain rules when it shows employees are definitely not up to date, while the rules when considered up to date are too complicated to be programmed (word of users..) and they want decide and do it manually.

So in other words, employees are only considered up to date when they manually approve them, however after approval if some documents are expired for example they want the approval to be cancelled.

PS, Appreciate your input.

Thanks,
Ben
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41738283
Sometimes you just have to go with the flow.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question