Running a Query Using a Text File for Input

  • 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.

  • 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

     

  • 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
  • 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).

  • 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

  • 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

  • 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..........

  • 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

  • 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.

  • 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.

     

  • Why not use T-SQL to generate the queries to be executed?  Then cut and paste the result of this into a text file to be executed using OSQL.

    For example,

    SELECT DISTINCT

    '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 = '''+C.CustomerId+''''

    FROM Customers C

         INNER JOIN

         Orders O ON C.CustomerID = O.CustomerID

  • Since we're allowed to use QA and presumably access other DBs on the server, why not BCP in into a DB the list of customers, then use osql to run a single query, joining to the tables in the vendor's DB?

    The vbs solution requires 17 000 queries, which, IMHO, is ludicrous.

    P

  • I agree. vbs and xls are helpful, at times, but yours is the better solution.

  • Hello Paul,

    Yes, please post more details on using the Jet 4.0 text driver for this type of query. Others have also mentioned Reporting Services and also trying to determine if there is a query that can be done straight from the database by talking to the users and finding out their logic for selecting records in the first place.

    Thanks,

    webrunner

    P.S. I think this discussion is great. Depending on the situation, you never know which method will be available, so it is good to know as many as possible.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Since folks are casting around for the "more than one way to do it" - my tendency would be to use perl and sqsh.   Has the advantage of working equally well from a Unix or Windows client, and works whether the database is MS SQL Server, Sybase, or both.

    Which simply reflects my own background and circumstances as does the original authors.

    Roger L Reid

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply