April 1, 2007 at 8:40 pm
Hey people.
Firstly, yes, I am an ultra noob when it comes to ms sql. However, I have been asked to write a script for a database that essentially goes through automatically and daily (ie. scheduled) and says:
For each racord, fieldZ = fieldx*fieldy
If anyone could point me in the right direction (ie e-books, on-line help, etc), it would be greatly appreciated!
Thank in advance!
April 2, 2007 at 3:34 am
Hi there,
You might want to look at computed columns in SQL Server Books Online (BOL) as this might well give you what you want without having to run the script each day. If you want to run it each day then have a look at Scheduled Jobs and UPDATE and you should be able to do this.
- James
--
James Moore
Red Gate Software Ltd
April 2, 2007 at 7:43 am
If you just want to update all rows, it's a simple update statement. A computed column will work, but if you have exceptions or things change, then it's harder to work with. The basic UPDATE statement works against all rows that match a TRUE in the WHERE clause. Like this:
UPDATE MyTable
SET FieldX = FieldY * FieldZ
WHERE SomeColumn = SomeValue
if you leave off the where clause, then all rows are changed.
You can schedule this as a job in SQLAgent and put the code in the job step. However I'd make it a stored procedure to make the code easier to maintain and read. Wrap the code above like this:
CREATE PROCEDURE MyUpdateProc
as
UPDATE MyTable
SET FieldX = FieldY * FieldZ
WHERE SomeColumn = SomeValue
RETURN
Then in the job, pick the TSQL command and enter "exec MyUpdateProc" in the code area. Be sure the right database is selected.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply