Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Need help with an MSSQL script

I have been given the following script by an application support to run.
The objective is to correct a specific problem in the database with one of the tables.
-----------------------------------------------------------------
Begin tran
update gbkmut
set transtype = 'V'
where faktuurnr in ('20033678','20030763')
and datum = '2014-03-28 00:00:00.000'
and ltrim(periode) = '12'
Rollback tran
----------------------------------------------------------------
There is a separate list of values for the field, faktuurnr, available in a spreadsheet.
Instructions I have received is to substitute these values in the script above.
The list runs into hundreds of items.
I raised the possibility of the script referring to an external csv file which will permit less chance of errors whilst manually substituting. And also allow for easy checking.
I was told that it could not be done.
Not being an SQL programmer myself, I could not very well dispute that.
But I am still skeptical - at least not until an Expert(s) have had a look first.
Your comments would be most appreciated.
Thanks.
0
garychu
Asked:
garychu
3 Solutions
 
Wasim Akram ShaikCommented:
Well you don't have to be expert to write a script.. use a little bit of excel to do this task.. excel has concatenate function which can combine two cells together.. place your SQL in one of those cells and accomplish this..
There are lot of samples around this all over web.. I found this out for
your reference
http://www.excel-formulas.com/sample-question/Create-SQL-Query-From-Columns
0
 
Scott PletcherSenior DBACommented:
Load the Excel spreadsheet into a table and INNER JOIN to that table.

You can right-click on the database, select "Tasks" and "Import Data..." to import the spreadsheet.
0
 
PortletPaulCommented:
Why are you rolling back?

-----
Scott's proposal is a good one, do you know the syntax?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
garychuAuthor Commented:
Thanks, Experts.
1) I am not sure I understood Wasim's suggestion.
By concatenating a number of discrete items, how do I substitute each item in the given script?

2) I have no idea how to INNER JOIN to a new table containing the required items.
Can this be incorporated in the script? Would help if I could then run it by the application support people who otherwise will not take responsibility.

The reason for the rollback is to allow a chance to check the outcome first before
committing the update.

Thanks again in the meantime.
0
 
sameer2010Commented:
Try the attached. You can tailor it to suit additional query conditions, etc.
Updatexls.xlsx
0
 
garychuAuthor Commented:
Thanks, sameer2010.
If I understand it correctly, the approach is to build up the number of items within the parenthesis in the line;
     where faktuurnr in ('20033678','20030763')

But is there a maximum number of comma separated items that can be included in this way? I expect that there will be hundreds.
0
 
Wasim Akram ShaikCommented:
Its just an example what I and sameer had suggested, as our experts suggested, loading into table in database is also a very good suggestion..

As you are novice in this, we thought excel would be easier for you to start.. you have to prepare up your own query in excel using concatenation to do this task.

else post your sample data of one record, our experts will help you to do that for sample.. later on you can apply it to all records(hundreds/thousands)
0
 
garychuAuthor Commented:
Thanks for the comments and suggestions, Experts.
But my question must have been improperly phrased because the crux of my problem was not directly addressed.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now