Output an SQL Query to a text file

Hi,

How would I go about writing the results of an SQL query to a text
file?

I cannot find any info in the Online help files.

For example, I would like the results of:

SELECT * FROM TableA

To be written to the file result.txt

AlexOpen query analyzer. Go to Query and select Result to file... and
during execution, it will prompt a file name...|||Thanks but I cannot do that.

This must query must be invisible to the end user.

For example, if a user performs some function on my system, I wish to
execute a query on what they have selected and output the results to a
file. I do not want my user to be selecting Save As etc..|||First your question is not clear. I am still not sure what you are
trying to accomplish.

First if you have a query that you dont want your user to know, wrap it
up in a stored proc and schedule it in a job and let the user execute
the job.

Another easy way is to create a dts and let the user run the dts that
should run the query and output the file.

Hope this helps..!|||Ok, I have no idea what a dts is!!

Is it possible to output the results from a stored procedure to a
specified file.

I do not want to use a tool or anything, just simply write the results
from a query to a file.|||Alex,
You might try adding this to your connection string: -o '<path
name>\results.txt.

such as: isql -SMyserver -Uusera -Puserapassword -i mysqlquery.sql -o
results.tx

hth

"Alex" <alex_seymour@.lineone.net> wrote in message
news:1108412990.821013.237700@.g14g2000cwa.googlegr oups.com...
> Hi,
> How would I go about writing the results of an SQL query to a text
> file?
> I cannot find any info in the Online help files.
> For example, I would like the results of:
> SELECT * FROM TableA
> To be written to the file result.txt
>
> Alex|||On 2/14/05 4:24 PM, in article
1108416279.303119.278950@.z14g2000cwz.googlegroups. com, "Alex"
<alex_seymour@.lineone.net> wrote:

> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

There is no OUTPUT TO FILE argument in a SELECT clause. You can SELECT INTO
a temporary table but this doesn't help you. The simplest thing to do (path
of least resistance) is to use a program in the middle to handle the query
and writing the recordset into a file. For example, if you wanted to create
an .ASP web form you could simply ask for the customer's email address, run
a query and send them the results. You could write a VB app or VB script to
run locally and generate a file from your query.

-Greg|||There isn't a direct method. Two possible workarounds are OSQL and
BCP. I tend to use BCP. BCP only lets you output from a permanent
table, though.

I would create a table called "CACHE" where one of the columns is "IP"
and the other cols match the SELECT query. Then store the output into
CACHE along with the user's IP. Then run BCP to output from CACHE.|||Alex (alex_seymour@.lineone.net) writes:
> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

No, you don't want to do that. Yes, you can invoke xp_cmdshell and from
there do all sorts of things. But then your users would have to have
permission to use xp_cmdshell, and that could be a security issue.

It's not clear why you want to write to a file, but if you want to
log some stuff for debug reasons - write to a table instead. You are
in a database, not in a file system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok this is great, the tables I need data outputting from are permanent.

Basically it is for creating a log file. The database is huge, but the
log only needs to contain the contents of a few tables. It has to go to
a file because I want to be able to save the results of the query to
disk or print it out.

Can I BCP out from several tables into the same file?

i.e. I need SELECT * from TableA, TableB, TableC to result.txt

Security is not an issue.|||Alex wrote:
> Ok this is great, the tables I need data outputting from are
permanent.
> Basically it is for creating a log file. The database is huge, but
the
> log only needs to contain the contents of a few tables. It has to go
to
> a file because I want to be able to save the results of the query to
> disk or print it out.
> Can I BCP out from several tables into the same file?
> i.e. I need SELECT * from TableA, TableB, TableC to result.txt
> Security is not an issue.

It's a little involved but you can write a select statement joining
several tables. Each time BCP executes, I believe it overwrites the
text file.

Check out this article:
http://www.sqlteam.com/item.asp?ItemID=4722

OUTER JOIN with multiple tables and a plus sign?

I am trying to select specific columns from multiple tables based on a
common identifier found in each table.

For example, the three tables:

PUBACC_AC
PUBACC_AM
PUBACC_AN

each have a common column:

PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier

What I am trying to select, for example:

PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip

where the TABLE.unique_system_identifier is common.

For example:

--------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES

--------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212

--------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210

When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:

--------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
--------------------

I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.

Help, please?

P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.

Thanks in advance for your help!

NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.

Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
(Rhetorical question only.)You do the following:

SELECT PUBACC_AC.Name + PUBACC_AM.Phone_number + PUBACC_AN.zip
FROM PUBACC_AC
INNER JOIN PUBACC_AM
ON PUBACC_AM.unique_col = PUBACC_AC.unique_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.unique_col = PUBACC_AC.unique_col

That's it - you do inner join, if you want only those records, for which
unique_col value exists in all 3 tables.
Or, if you replace "INNER JOIN" with "FULL JOIN", which is the same as OUTER
JOIN in Oracle, you will get
as meny records as number of unique_col values.

Thats it!

Hope it helped,
Andrey aka Muzzy

"TeleTech1212" <tele_tech1212DELETE_THIS@.yahoo.com> wrote in message
news:Xns9556E99BEE9B4teletech1212DELETETH@.207.115. 63.158...
> I am trying to select specific columns from multiple tables based on a
> common identifier found in each table.
> For example, the three tables:
> PUBACC_AC
> PUBACC_AM
> PUBACC_AN
> each have a common column:
> PUBACC_AC.unique_system_identifier
> PUBACC_AM.unique_system_identifier
> PUBACC_AN.unique_system_identifier
>
> What I am trying to select, for example:
> PUBACC_AC.name
> PUBACC_AM.phone_number
> PUBACC_AN.zip
> where the TABLE.unique_system_identifier is common.
>
> For example:
> --------------
> PUBACC_AC
> =========
> unique_system_identifier name
> 1234 JONES
> --------------
> PUBACC_AM
> =========
> unique_system_identifier phone_number
> 1234 555-1212
> --------------
> PUBACC_AN
> =========
> unique_system_identifier zip
> 1234 90210
>
> When I run my query, I would like to see the following returned as one
> blob, rather than the separate tables:
> --------------------
> unique_system_identifier name phone_number zip
> 1234 JONES 555-1212 90210
> --------------------
>
> I think this is an OUTER JOIN? I see examples on the net using a plus
> sign, with mention of Oracle. I'm not running Oracle...I am using
> Microsoft SQL Server 2000.
> Help, please?
> P. S. Will this work with several tables? I actually have about 15
> tables in this mess, but I tried to keep it simple (!??!) for the above
> example.
> Thanks in advance for your help!
> NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
> ADDRESS.
> Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
> (Rhetorical question only.)

OUTER JOIN issue

Hi all,
I have two tables (for example, table1, table2) where table1 holds thesame data as table2 but also has other rows that are no contained intable2.
Now if I performed the following query...
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
... I expect to get the rows that are not contained in both tables. Butfor some reason I don't get this. I get all the rows from tabel1.
Is my thinking of what theLEFT OUTER JOIN query wrong, or is the query wrong?
To get around my problem, I had to do the following
SELECT table1.name
FROM table1
WHERE table1.table_name not IN (SELECT table_name FROM table2)
I would have preferred to have solved this with theLEFT OUTER JOIN though
Thanks
Tryst
I think you are a little confused about outer joins...
Left/Right Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions
Say I have
Table1
Id Name Age
-----
1 a 20
2 b 30
3 c 22
Table2
Id Sex
---
1 M
2 F
Say I want id,name,age,sex(if available) then I would use left join
Select id,name,age,sex from table1 left join table2 on table1.id = table2.id
This will return
Id name age sex
------
1 a 20 M
2 b 30 F
3 c 22 <null>
|||You need to add a WHERE condition to return the results you want from the LEFT OUTER JOIN:

SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
WHERE table2.table_name IS NULL

What would probably perform better for you is this:

SELECT table1.name
FROM table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.table_name = table2)

Check both execution plans/execution time in Query Analyzer to see which one performs better.

|||Ok, thanks guys.
Why is the inner SELECT query selecting NULL from table2? Whats it use?
Tryst
|||

Tryst wrote:

Why is the inner SELECT query selecting NULL from table2? Whats it use?

The list of column names is immaterial for the EXISTS. What isimportant is the WHERE condition. I have a habit of using NULL,partly because I read somewhere that it uses less resources than a * ora specific column (I am not sure whether or not that is true).

osql :r filename

Hello,
somebody have already used this option od osql ==> :r filename
Could you give me some example
thanksPut below in a bat file. It will create an inner script file as sell as an outer script file and
then call OSQL with the outer script file as input file.
ECHO SELECT 'Inner script' > inner.sql
ECHO SELECT 'Outer Script before' > outer.sql
ECHO :r inner.sql >> outer.sql
ECHO SELECT 'Outer cript after' >> outer.sql
OSQL /E /i outer.sql
PAUSE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> Hello,
> somebody have already used this option od osql ==> :r filename
> Could you give me some example
> thanks|||Thanks for your response.
I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
and scr2.sql
these scripts contains "go" statement".
there is the contain of "all.sql"
:r scr1.sql
:r scr2.sql
when I run this command on line
osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
I obtain the error message : "Incorrect syntax near 'GO'.
If I run the scr1.sql directly from a command line, that is ok. There is no
error message.
Do you have any idea about this problem.
Thanks
"Tibor Karaszi" wrote:
> Put below in a bat file. It will create an inner script file as sell as an outer script file and
> then call OSQL with the outer script file as input file.
> ECHO SELECT 'Inner script' > inner.sql
> ECHO SELECT 'Outer Script before' > outer.sql
> ECHO :r inner.sql >> outer.sql
> ECHO SELECT 'Outer cript after' >> outer.sql
> OSQL /E /i outer.sql
> PAUSE
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ft" <ft@.discussions.microsoft.com> wrote in message
> news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> > Hello,
> >
> > somebody have already used this option od osql ==> :r filename
> >
> > Could you give me some example
> >
> > thanks
>
>|||An inner script called by OSQL using :r cannot contain GO, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:98DBCA95-E826-48D3-BE60-1D10EBFC8E79@.microsoft.com...
> Thanks for your response.
> I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
> and scr2.sql
> these scripts contains "go" statement".
> there is the contain of "all.sql"
> :r scr1.sql
> :r scr2.sql
> when I run this command on line
> osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
> I obtain the error message : "Incorrect syntax near 'GO'.
> If I run the scr1.sql directly from a command line, that is ok. There is no
> error message.
> Do you have any idea about this problem.
> Thanks
>
> "Tibor Karaszi" wrote:
> > Put below in a bat file. It will create an inner script file as sell as an outer script file and
> > then call OSQL with the outer script file as input file.
> >
> > ECHO SELECT 'Inner script' > inner.sql
> > ECHO SELECT 'Outer Script before' > outer.sql
> > ECHO :r inner.sql >> outer.sql
> > ECHO SELECT 'Outer cript after' >> outer.sql
> > OSQL /E /i outer.sql
> > PAUSE
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "ft" <ft@.discussions.microsoft.com> wrote in message
> > news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> > > Hello,
> > >
> > > somebody have already used this option od osql ==> :r filename
> > >
> > > Could you give me some example
> > >
> > > thanks
> >
> >
> >