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 Thursday, March 1, 2007 7:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:17 PM
Points: 388, Visits: 10,781

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

Post #348476
Posted Thursday, March 1, 2007 7:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 428, Visits: 928
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



Post #348480
Posted Thursday, March 1, 2007 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 4:40 PM
Points: 5, Visits: 77
I agree. vbs and xls are helpful, at times, but yours is the better solution.


Post #348496
Posted Thursday, March 1, 2007 7:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 2,335, Visits: 2,670
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.


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #348499
Posted Thursday, March 1, 2007 8:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:32 AM
Points: 108, Visits: 166

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
Post #348521
Posted Thursday, March 1, 2007 8:25 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

Cannot be used in the situation we are discussing at present. This T-SQL would generate statements for every single customer that made an order, but what we need is something to handle a large subset of a large customer base provided in the form of text file.

In other circumstances T-SQL would do just great. I use it often especially for admin tasks.

Post #348529
Posted Thursday, March 1, 2007 8:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 266, Visits: 2,568
I, too, am tickled by this great discussion. I appreciate the author who wrote such a clear original article and then seeing the different ways other people would solve this.

Personally, I would use the Jet/Access method myself. However, if there were a reason I couldn't use Access, I would create the set of queries in Excel. Then the question is how to run them. I like the Author's suggestion of OSQL. I also like the DOS suggestion posted earlier. Both of these are areas I will research more. Thanks!
Post #348531
Posted Thursday, March 1, 2007 8:28 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
R L, give us some guidelines or short examples for the benefit of people that are not familiar with perl and sqsh. Hopefully something that makes solution quick and easy.
Post #348532
Posted Thursday, March 1, 2007 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2008 2:59 PM
Points: 4, Visits: 3

At my company we run into "requests" like this all the time.  Us lowly developers don't have the right to create jack in the production database but the data results the business unit wants come from there.  Our normal solution is to use Excel.  In a Query analyzer window we'll do a create table statement to create a temporary table to hold the data, in this case "create table #tmp (cust varchar(5))"

We'll have a list of clients sent to us in Excel or a text file.  Opening the attachment in Excel.  We'll insert a blank column in front of the data column and define a formula as ="insert into #tmp values('" & A2 & "')"  Copy the formula in A1 to the entire A column.  Copy the column and do a Past...Special...Values.  Copy the column of insert statements into QA and run.  Now you have a temp table in a QA session you can join to the data in the database and use in whatever ways one needs.

We deal with payroll data for millions of people employed throughout the country and it is not uncommon to get a request to pull demographic info on 10-20K people at a time for some report or another.  This method makes these requests trivial to handle.




Shuffle Up and Deal!
Post #348552
Posted Thursday, March 1, 2007 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 5, 2012 12:03 PM
Points: 117, Visits: 163
Howdy All,

I agree that authoring a single select per CustomerID in the key file is wasteful and there is no need for the vbs script in this case. This also sounded like a one-off sort of request, so I thought I would offer a method that did not use a linked server.

Run the following query, either through the command line as in the article or in Query Analyzer and set your output options to create the desired output file.

SET NOCOUNT ON

SELECT
C.CustomerID,
C.CompanyName,
C.City,
O.OrderDate,
O.Freight
FROM
dbo.Customers AS C
INNER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID
INNER JOIN OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\;Extended Properties="text;HDR=No;FMT=Delimited"'
)...custID#txt AS T ON T.F1 = C.CustomerID

Some things of note;
• The Data Source is the directory where the file being read is
• If there are no Header Rows (HDR=No) the column names are F1, F2, F3 and so on.

Cheers
Post #348554
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse