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.
garychuAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior 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
 
Wasim Akram ShaikConnect With a Mentor Commented:
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
 
PortletPaulfreelancerCommented:
Why are you rolling back?

-----
Scott's proposal is a good one, do you know the syntax?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
sameer2010Connect With a Mentor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.