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
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
Can you rewrite the question using 3 records, 3 fields each? Highlight changes between lines.
ASKER
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
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
You could use SQLCmd for this purpose.
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.
Same disclaimer applies.
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'
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!
ASKER
<<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
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
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.
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.
you mentioned parameters. Do you have a version of the data that isn't embedded in the SQL?
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.
Good question.
@TomWinslow
The comments you have accepted as the solution to your problem do not seem to be solutions. Did you accept them prematurely?
The comments you have accepted as the solution to your problem do not seem to be solutions. Did you accept them prematurely?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OMG! I misread your reaaponse. You are correct.
Thank you sir
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.