Solved

Need help with an MSSQL script

Posted on 2014-04-15
8
185 Views
Last Modified: 2014-04-19
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
Comment
Question by:garychu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 200 total points
ID: 40002644
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 100 total points
ID: 40002749
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40002813
Why are you rolling back?

-----
Scott's proposal is a good one, do you know the syntax?
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:garychu
ID: 40002931
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
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 200 total points
ID: 40003164
Try the attached. You can tailor it to suit additional query conditions, etc.
Updatexls.xlsx
0
 

Author Comment

by:garychu
ID: 40003180
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40003242
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
 

Author Closing Comment

by:garychu
ID: 40010916
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

615 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