Groups | Blog | Home
all groups > sql server new users > september 2006 >

sql server new users : SQL script to change multiple fields.


Sam Commar
9/15/2006 12:00:00 AM
I have a database where I need to change all period to post information back
1 year.

There are approx 400 tables and most of them have a perpost field where the
Period to post information is stored. I need to take this information back
12 months for all perpost fields. eg Perpost in APDOC table in a line might
be 199912 and I will need to change it to 199812.

Is there a dynamic script that could be created to do this.

Thank you so much for your assistance.

Sam Commar

Arnie Rowland
9/15/2006 9:35:06 AM
You could use a WHILE loop to cycle through INFORMATION_SCHEMA.TABLES.

You could use the undocumented system stored procedure =
sp_MSForEachTable. If this is a one time process, this should be ok, it =
you are creating a process to use in the future, there is no certainty =
that it will be usable at a later time. See:

http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

EXECUTE sp_MSforeachtable "UPDATE '?' SET PerPost =3D ( PerPost - 100 )"

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous


[quoted text, click to view]
Arnie Rowland
9/15/2006 11:06:01 AM
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:

Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).

If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.


--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


[quoted text, click to view]

AddThis Social Bookmark Button