Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running a Query Using a Text File for Input


Running a Query Using a Text File for Input

Author
Message
rscarlson
rscarlson
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 6

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rcarlson/2801.asp

The original solution required much more than this article suggested. The response from everyone emphasized the need to always examine the alternatives. Sometimes the easiest solution is not the best and vice versa. I am very happy that this article generated an active response. The comment about what users want and what they need is an important one to remember.

Thanks for the great discussion.





Anatol Romanov-404520
Anatol Romanov-404520
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 18

Nice solution. As a DBA I faced similar tasks many times. My usual way of completing them is by using Excel:

1. Start with 1 column

ALFKI
ANTON
AROUT
BERGS
BOLID
BONAP
BSBEV
CACTU
CONSH
DOGGY
FOLIG
FOLKO
FRANK
FRANR
FRANS
FURIB

2. Insert a column in front of it

3. Type

Placeholder1

in the first cell of this column. Drag and drop the value to populate the whole column.

4. Type

Placeholder2

in the first cell of the 3rd column. Drag and drop the value to populate the whole column.

5. "Save As" to a text file

6. Open the saved text part in notepad. do Edit/Replace replacing

Placeholder1 with

SET NOCOUNT ON SELECT C. CustomerID, C. CompanyName, C.City, O. OrderDate, O. Freight FROM Customers C INNER JOIN Orders O " ON C.CustomerID = O.CustomerID WHERE C.CustomerID = '

then replacing Placeholder2 with a single quote. Make sure all TABs and Commas are replaced with empty strings as well.

Save the output.

It is more simple than it sounds!

Regards,

Anatol Romanov


Dave Dustin
Dave Dustin
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 131

whereas my solution was to write an application in C++ which lets me use a placeholder in a sql file, and specify a tab delimited text file to provide the values.

-- test.sql

SELECT TestTable.RowID, TestTable.RowName FROM TestTable WHERE TestTable.RowID = '@1'

-- input.txt

123
321
456
654

-- console

querysql.exe -server=(local) -database=testdb -sql=test.sql -input=input.txt -output=output.txt

very handy because I don't have to create any tempoary files with all the sql statements. Output can be either to screen or to a file. Works with either a ODBC-DSN or direct to the SQL server.

Would this be of interest to anybody else?



AucklandSQL User Group
Independent SQL Server Consultant
SQL Server MVP

Paul Thornett
Paul Thornett
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 96
I prefer to use a linked server using the Jet 4.0 text driver. This technique allows me to treat the incoming text file as a table, so I can join to it in the normal way. If anyone is interested, I can provide more detail on this. It's a technique I use a lot when dealing with tab-delimited files which are used either for input or output (yes, I can also INSERT new rows into my text files).



Daniel van der Meulen
Daniel van der Meulen
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

Group: General Forum Members
Points: 502 Visits: 165

Hi,

I like the approach to solving the problem, but the VBscript seems like overkill. I've also used excel several times to solve these problems. But when they are this easy why not use good old DOS:

FOR /F %i in (custID.txt) do @echo set nocount on select %i >> custID.qry

This solved your problem in just one line of code. For more information on the FOR command, just type "help for" on the command line.

Daniel


Stefan Morrow
Stefan Morrow
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 2
Hi there,

this topic brings me actually to another issue. Considering the original limitations of query and report only, would it have been acceptable to write a stored procedure? That is obviously if the server is 2005 and has CLR enabled.

I had some similar topics not too long ago to implement data pushing usijng C# stored procedures rather than using polling mechanisms to update client workstations.

Using a stored procedure to output to a text file would allow you to communicate with outside processes as well as build a bridge for older or incompatible systems.

As I am new, please allow me the question: have there been any topics on CLR stored procedures?

Stefan
Predrag Miletic
Predrag Miletic
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1

This article gave me a nice insight into vbs since I did not use it for a long time, but agree with you guys that is a bit of overkill. I would use Excel myself, but have to admit that the other 3 suggestions are very nice (Dave's querysql, Paul's Jet and Daniel's DOS).

Another solution would be MS Access (even though personally it is my last resort): import text file, link the other tables from SQL and create report.

Paul, could you please provide us with more details on your Jet 4.0 text driver solution.

Rgds


Richard Gardner-291039
Richard Gardner-291039
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 421

Interesting tecchy answers......

To my mind the problem would be better solved by asking the user what their criteria is for choosing the 17,000 customers in the first place, there must have been some reason and therefore logic behind the distinction, then write that query instead and you've saved the user the bother of ever doing the same thing again....

If I spent my life doing what users told me instead of what they need to be done I'd be an Excel expert..........


Predrag Miletic
Predrag Miletic
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1

Absolutely agree Richard. But assumption here is that one would do what you suggest by default and not just jump every time end user says hop (well, unless you are junior DBA ;-)).
But once your discussion with requestor is exhausted and you end up with task like this then tech solutions discussed here is exactly what you need.


Richard Gardner-291039
Richard Gardner-291039
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 421

Fair enough - it could be a useful solution, I agree.

My personal preference these days is reporting services - it takes 5 minutes to set up a data source, run a query and deploy to the intranet, then dump it to Excel.

Once you've taught your users how to use autofilters you can dump anything you want in half an hour and everyone is happy.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search