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
tripleAxe
tripleAxe
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 13028

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


schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 1289
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



jancorley
jancorley
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 77
I agree. vbs and xls are helpful, at times, but yours is the better solution.



webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3039 Visits: 3760
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.

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
Roger L Reid
Roger L Reid
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 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
Predrag Miletic
Predrag Miletic
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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.


JJ B
JJ B
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 2846
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!
Predrag Miletic
Predrag Miletic
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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.
finaltable
finaltable
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: 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!
Dennis D. Allen
Dennis D. Allen
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
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
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