SQL Clone
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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2962 Visits: 13748

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
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5138 Visits: 1462
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
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

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



webrunner
webrunner
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13012 Visits: 4086
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 Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 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
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 2862
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
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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 (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

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