How do I batch or automate execution of 3,700 one-line, similar, SQL statements?

I have a project that requires me to execute approximately 3,700 individual SQL statements using either MS Access (that I prefer) or SQL Management STUDIO 2008. All of the SQL statements are ‘one-liners’ and similar or alike except for parameters. Here is an example:

update pxdta.f4311 set pdprrc=0, pdaexp=0, pdaopn=0, pdvr01='116641' where pdkcoo='00221' and pddoco=252135 and pdlitm='(CFE)G688883-1' and pdlnid=2 and pdprjm=761060 and pddcto='OH'      761060      00221      252135      OH      (CFE)G688883-1      2      0      0      0      116641
update pxdta.f4311 set pdprrc=0, pdaexp=0, pdaopn=0, pdvr01='116641' where pdkcoo='00221' and pddoco=252135 and pdlitm='(CFE)G688883-1' and pdlnid=3 and pdprjm=761060 and pddcto='OH'      761060      00221      252135      OH      (CFE)G688883-1      3      0      0      0      116641
update pxdta.f4311 set pdprrc=0, pdaexp=0, pdaopn=0, pdvr01='116641' where pdkcoo='00221' and pddoco=252135 and pdlitm='(CFE)G688883-1' and pdlnid=4 and pdprjm=761060 and pddcto='OH'      761060      00221      252135      OH      (CFE)G688883-1      4      0      0      0      116641
update pxdta.f4311 set pdprrc=73, pdaexp=10.22, pdaopn=10.22, pdvr01='215099' where pdkcoo='00221' and pddoco=252093 and pdlitm='DHW026' and pdlnid=3 and pdprjm=760972 and pddcto='OH'      760972      00221      252093      OH      DHW026      3      73      10.22      10.22      215099
update pxdta.f4311 set pdprrc=76, pdaexp=3.04, pdaopn=3.04, pdvr01='215055' where pdkcoo='00221' and pddoco=252086 and pdlitm='H53081-39' and pdlnid=13 and pdprjm=761309 and pddcto='OH'      761309      00221      252086      OH      H53081-39      13      76      3.04      3.04      215055
update pxdta.f4311 set pdprrc=76, pdaexp=3.04, pdaopn=3.04, pdvr01='215055' where pdkcoo='00221' and pddoco=252086 and pdlitm='H53081-39' and pdlnid=14 and pdprjm=761292 and pddcto='OH'      761292      00221      252086      OH      H53081-39      14      76      3.04      3.04      215055
update pxdta.f4311 set pdprrc=76, pdaexp=6.08, pdaopn=6.08, pdvr01='215047' where pdkcoo='00221' and pddoco=252084 and pdlitm='H53081-39' and pdlnid=11 and pdprjm=761291 and pddcto='OH'      761291      00221      252084      OH      H53081-39      11      76      6.08      6.08      215047
update pxdta.f4311 set pdprrc=143, pdaexp=2.86, pdaopn=2.86, pdvr01='215096' where pdkcoo='00221' and pddoco=252092 and pdlitm='H56073-39' and pdlnid=12 and pdprjm=760907 and pddcto='OH'      760907      00221      252092      OH      H56073-39      12      143      2.86      2.86      215096
update pxdta.f4311 set pdprrc=143, pdaexp=57.2, pdaopn=57.2, pdvr01='215047' where pdkcoo='00221' and pddoco=252086 and pdlitm='H56073-39' and pdlnid=15 and pdprjm=760996 and pddcto='OH'      760996      00221      252086      OH      H56073-39      15      143      57.2      57.2      215047

There must be a way to execute them as a batch. I am attaching the entire list as a Excel sheet.

HELP!

Tw
Tom WinslowAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fhlio_adminCommented:
there are several ways you can do this...

the simplest way that I can think of since you obviously know how to make the SQL in Excel and you have access to SQL Management Studio 2008 is to copy/paste your single statements in to the SQL Query Analyzer and run it.  You can paste multiple statements at a time and they will execute sequentially.

Another way that you can do this is to link the table in Access and use the SQL statement in a Docmd.RunSQL in a VBA module.  I am not a fan of this option as it would not be as efficient as the first.

DISCLAIMER: Neither of the above cases have handled errors in the routine.  This means that it is merely assumed that this has processed appropriately.  To confirm that it has, you maybe could run a union query of the desired end result to determine if the results took effect.  There are also numerous ways that error checking can be done.

The possibilities are plentiful.
0
hnasrCommented:
Can you rewrite the question using 3 records, 3  fields each? Highlight changes between lines.
0
Tom WinslowAuthor Commented:
Below are three records with only the SQL code. The Excel sheet contains additional columns beyond the SQL statements in column A.

Can I setup a loop to process the statements? Suggestions?

update pxdta.f4311 set pdprrc=73, pdaexp=10.22, pdaopn=10.22, pdvr01='215099' where pdkcoo='00221' and pddoco=252093 and pdlitm='DHW026' and pdlnid=3 and pdprjm=760972 and pddcto='OH'

update pxdta.f4311 set pdprrc=76, pdaexp=3.04, pdaopn=3.04, pdvr01='215055' where pdkcoo='00221' and pddoco=252086 and pdlitm='H53081-39' and pdlnid=13 and pdprjm=761309 and pddcto='OH'

update pxdta.f4311 set pdprrc=76, pdaexp=3.04, pdaopn=3.04, pdvr01='215055' where pdkcoo='00221' and pddoco=252086 and pdlitm='H53081-39' and pdlnid=14 and pdprjm=761292 and pddcto='OH'

tw
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
You could use SQLCmd for this purpose.
0
fhlio_adminCommented:
If you put the data inside a table in access (or linked the excel workbook in access) you could iterate through the recordset and use the fields in the Docmd.RunSQL statement.

Same disclaimer applies.
0
hnasrCommented:
What I asked for was like this, to highlight what to be changed in different lines.

update pxdta.f4311 set pdprrc=73, pdaexp=10.22, pdaopn=10.22, pdvr01='215099' where pdkcoo='00221' and pddoco=252093 and pdlitm='DHW026' and pdlnid=3 and pdprjm=760972 and pddcto='OH'

update pxdta.f4311 set pdprrc=76, pdaexp=3.04, pdaopn=3.04, pdvr01='215055' where pdkcoo='00221' and pddoco=252086 and pdlitm='H53081-39' and pdlnid=13 and pdprjm=761309 and pddcto='OH'

update pxdta.f4311 set pdprrc=76, pdaexp=3.04, pdaopn=3.04, pdvr01='215055' where pdkcoo='00221' and pddoco=252086 and pdlitm='H53081-39' and pdlnid=14 and pdprjm=761292 and pddcto='OH'

Looks line all values change.
Where do you get these values from?
An acceptable suggestion from  fhlio_admin!
0
Tom WinslowAuthor Commented:
<<What I asked for was like this, to highlight what to be changed in different lines.>>

In the example above, you have acually highlited what will change from line-to-line. Each time I tried to highlight a word or number, I got html code displayed. I was not sure if that would be confusing or not.

tw
0
hnasrCommented:
html code appears, but will show correctly when previewed aor submitted.

If you have the values in a table, you can automate the update process.

To see how, upload a sample database with a table and test data to update. Add another table with the new values. The structure of the new data file can be the same as the original table regarding column names.
0
aikimarkCommented:
you mentioned parameters.  Do you have a version of the data that isn't embedded in the SQL?
0
fhlio_adminCommented:
Can someone please explain to me the reason for the accepted solution?  I don't understand how those answers wound up being the solution to the question.
0
Anthony PerkinsCommented:
Good question.
0
aikimarkCommented:
@TomWinslow

The comments you have accepted as the solution to your problem do not seem to be solutions.  Did you accept them prematurely?
0
Tom WinslowAuthor Commented:
Referring to the excepted solutions, as it turns out, I am embarrassed by the question that I asked and was trying to figure a graceful way to get out of it.

As you have probably discerned, I am not highly skilled using SQL 2008 Management Studio. One of the IT guys dropped by my office, looked at what I was doing, and said, “All you have to do is drop all of these statements into SQL 2008, pull trigger, and it will run automatically. You do not have to worry about all of the other stuff. In other words, he politely told me that I was trying to make an easy job hard. Therefore, I dropped the 6000+ lines of code into SQL Studio 2008 it ran in a matter of seconds. I really did not use any solution from Experts Exchange – instead, I used the solution from one of out IT folks.

It seems that, due to my inexperience, I was trying to create a problem where none existed. For the efforts expended that by the experts, I split the points and ended the session and went on about my business. I hope that I did not break any rules or upset anybody with the course of action.

Over the years, I have found experts exchange to be an invaluable resource for me here my company and the experts invaluable. I learn every time I ask a question.
0
fhlio_adminCommented:
According to what I understand, you DID follow my suggestion from my first post...


the simplest way that I can think of since you obviously know how to make the SQL in Excel and you have access to SQL Management Studio 2008 is to copy/paste your single statements in to the SQL Query Analyzer and run it.  You can paste multiple statements at a time and they will execute sequentially.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tom WinslowAuthor Commented:
OMG! I misread your reaaponse. You are correct.
0
fhlio_adminCommented:
Thank you sir
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.