Solved

Need help with an MSSQL script

Posted on 2014-04-15
8
177 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
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:
ScottPletcher 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 48

Expert Comment

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

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

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video teaches viewers about errors in exception handling.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now