Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Running a Query Using a Text File for Input Expand / Collapse
Author
Message
Posted Wednesday, January 10, 2007 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 8:36 AM
Points: 28, 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.




Post #335844
Posted Wednesday, February 28, 2007 10:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 11:38 PM
Points: 10, 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

 

Post #348382
Posted Thursday, March 1, 2007 1:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 4:19 AM
Points: 39, Visits: 121

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
Post #348394
Posted Thursday, March 1, 2007 1:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, 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).


Post #348396
Posted Thursday, March 1, 2007 1:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 8:26 AM
Points: 500, Visits: 130

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

Post #348397
Posted Thursday, March 1, 2007 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 24, 2008 1:32 PM
Points: 2, 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
Post #348404
Posted Thursday, March 1, 2007 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2007 3:45 AM
Points: 5, 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

Post #348428
Posted Thursday, March 1, 2007 3:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 238, 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..........

Post #348429
Posted Thursday, March 1, 2007 4:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2007 3:45 AM
Points: 5, 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.

Post #348431
Posted Thursday, March 1, 2007 4:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 238, 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.

 

Post #348433
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse