?
Solved

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

Posted on 2012-08-26
17
Medium Priority
?
481 Views
Last Modified: 2012-09-10
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
0
Comment
Question by:Tom Winslow
  • 5
  • 4
  • 3
  • +2
16 Comments
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38335071
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38335090
Can you rewrite the question using 3 records, 3  fields each? Highlight changes between lines.
0
 

Author Comment

by:Tom Winslow
ID: 38335110
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38335126
You could use SQLCmd for this purpose.
0
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38335147
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38336028
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
 

Author Comment

by:Tom Winslow
ID: 38336164
<<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
 
LVL 31

Expert Comment

by:hnasr
ID: 38336358
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38336611
you mentioned parameters.  Do you have a version of the data that isn't embedded in the SQL?
0
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38352796
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38352852
Good question.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38352879
@TomWinslow

The comments you have accepted as the solution to your problem do not seem to be solutions.  Did you accept them prematurely?
0
 

Author Comment

by:Tom Winslow
ID: 38384118
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
 
LVL 6

Accepted Solution

by:
fhlio_admin earned 2000 total points
ID: 38384150
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
 

Author Closing Comment

by:Tom Winslow
ID: 38384195
OMG! I misread your reaaponse. You are correct.
0
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38384213
Thank you sir
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

809 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