printing to txt files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

printing to txt files

Author

Topic

DBoy129
Starting Member

2 Posts

Posted - 2005-11-05 : 22:07:50

Hey all,
I'm new to SQL and am having some troubles w/ a problem I was given.
I have two tables (OrderTableA & OrderTableB) which are linked by an OrderID. What I need to do is have the two tables printed out to two txt files. Below is the code I have so far.

CREATE TABLE OrderTableA(
OrderID NUMBER,
OrderDate CHAR(24),
CustomerID CHAR(8),
Total DEC);

CREATE TABLE OrderTableB(
OrderID NUMBER,
Quantity NUMBER,
Discount NUMBER,
UnitPrice DEC);

SELECT OrderID, "#" + OrderDate AS OrderDate, "&" + CustomerID AS CustomerID, Total
FROM OrderTableA;

SELECT OrderID, Quantity, Discount, UnitPrice
FROM OrderTableB;

The txt file for OrderTableA has to look like this:
!!OrderID OrderDate CustomerID Total
---------- ----------- ----------- ------
123456 #123456 &123456 123456

with the !! before OrderID in the header, the # before OrderID in the table and the & before the CustomerID in the table.

I'm not sure my code above is correct so far, and I've no idea how to print this to two txt files.

Any help would be greatly appreciated!! Thank you!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-06 : 08:08:38

You could use osql but I prefer bcp
osql is
create an sp
create proc s_extract_OrderTableA
as
select [!!OrderID] = OrderID ,
OrderDate = '#' + OrderDate ,
CustomerID = '&' + CustomerID ,
Total
from OrderTableA
go

then (set @fileloc and @name
declare @sql varchar(1000)
select @sql = 'osql -E -S' + @@servername + ' -d' + db_name() + ' -n -Q"exec s_extract_OrderTableA" -w1024 -s" " -o"' + @FileLoc + @name + '.txt"'
exec master..xp_cmdshell @sql

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DBoy129
Starting Member

2 Posts

Posted - 2005-11-06 : 10:20:31

I'm sorry, I didn't understand what the last part was doing. The Select statement made sense, but I got lost after that.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-06 : 13:49:05

The last bit is executing an osql command and redirecting the output to a file.
Have a look at osql in bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources