Select INTO TextFile ..... How to?

[VC]

Hi All,
I have a requirement to dump records from an SQL statement that joins 3
tables into a "|" delimited text file.
If it were one table and selected columns, I would readily use BCP. As there
are multiple tables involved, is there an optimal way to go about this?
Is there a Select Into textfile from table1, table2, table3 where.....

One other option that came to mind is to pull all the data into a temporary
table and then BCP out of that into a text file, which is a 2 step process
(First step gets data into temp table, Second step gets out of temp table
into text file)

Your help will be very much appreciated,
Thanks in advance for your time and have a good one,
V!

[Bret Halford]

You can create a view that does the join, then bcp out of the view.

The SQSH client has the ability to pipe output of a query to a file
http://www.isug.com/Sybase_FAQ/ASE/sqsh-faq-1.4.html

-bret

[Mark A. Parsons]

If the DB1 and DB2 databases are on the same dataserver just create your
view in DB2 to reference the tables in DB1, eg,

use DB2
go
create my_view as
select t1.col1,
t2.col4
from DB1.t1 t1,
DB1.t2 t2
where t1.key = t2.key
go

Then out in the OS run your bcp 'out' against DB2.my_view

--------------------------

If you're determined to do it from isql (or some other client front-end):

use DB1
go
select convert(varchar,t1.col1)+'|'+
convert(varchar,t2.col4)
from t1,
t2
where t1.key = t2.key
go

Then out in the OS you would grep your output for the '|' character and
dump the results into your 'bcp' file.

Or as Bret's already mentioned ... you could get sqsh to do something
similar like this for you.

(And, yeah, there are probably other ways to do this dependent on the
front end.)

VC wrote:

> Bret,
> Thanks for the pointers. Really appreciate your time.
> I am in a tricky situation where I only have Read-Only permissions on this
> database (say DB1).
> I am working from database DB2 on which I have Read-Write permissions.
> If I run a query on Box2 to pull data from DB1, then I would like to get the
> data directly into a file or a temp table/view in DB2. Is that possible....?
>
> Thanks again for your time and input,
> V!
>
> "Bret Halford" <br...@sybase.com> wrote in message
> news:42F2678F...@sybase.com...

[VC]

Thanks for your time and response Mark. Appreciate it.
Unfortunately, they are on different servers and therefore need to be pulled
from DB1 on Server1 into Server2.
Is there any light at the end of the tunnel for me, other than writing GL
scripts (C++, C#, VB)...?

Thanks again and have a good one,
V!
"Mark A. Parsons" <iron_horse@no_spamola_compuserve.com> wrote in message
news:42f2bd05@forums-1-dub...

[dken...@caci.com]

V,

        What I use to connect between two servers is component integration.  Here is what it requires.

1.  Add the remote server to the one where you want to see it:  exec sp_addserver [server alias for remote server], ASEnterprise, [remote server name]

2.  Then add an automatic remote login (my example is sa which I use):  exec sp_addexternlogin "[remote server alias]", "sa", "sa", "[sa password on remote server]"

        Once the connection is established, I create a proxy table on the local server that points to the remote server and it looks just like another table to my database.  I can then do my selections on it at any time like any other table.  I also use this to keep local tables up-to-date from the remote server.  Here is a sample of creating a local table name that points to the remote server table.

create proxy_table E_[name]
EXTERNAL TABLE at "[remote server alias].[remote database name].dbo.[remote table name]"
go

        Now I can use E_[name] like any other table.

        Does that help?

Dennis

[VC]

Hi Dennis,

That sounds amazing..... Thanks for your time and invaluable input.

 

A couple of questions though,

Do the local tables keep in sync with their "Master" tables when you do a "Select" on the local version or do I need to run any "Update/Refresh" periodically? or is this something that i do not have to worry.

 

Any other "gotchas" in terms of performance or traffic based on your experience?

 

Again, I am sure, I have to get this across to my DBAs to setup the whole remoting/integration.

 

Will keep you posted.

 

Thanks and have a good one,

V!

<dken...@caci.com> wrote in message news:42f37e3a$1@forums-1-dub...

[Mark A. Parsons]

You still have a couple options ... both of which I use on a regular basis:

1
==========
On the server with the DB1 database you can replace the 'use DB2' with
'use tempdb'.

Then create the view (see previous posting) in the tempdb database.

Afterwards bcp 'out' from tempdb..my_view.

Under normal circumstances you shouldn't have a problem creating a view
in tempdb. And since a view doesn't take up any room you wouldn't have
to worry about logging back into the DB1 dataserver to drop the view ...
just leave it there (it'll get wiped out next time the dataserver is
bounced).

I'd probably write the code to 'drop view my_view' just in case it
already exists, eg, left over from a previous run.

2
==========
A slight variation on the second method I gave:

select "GREP_ME:"+convert(varchar,int_col1)+"|"+
convert(varchar,date_col1,101)+"|"+
convert(varchar,char_col1)+"|"+
varchar_col1+":"
from whatever_table
go

Assuming you want to use a shell script you could feed the above query
into isql via a) a 'HERE' file method or b) putting the above query into
a SQL script and submit said script to isql.

Next you capture the output from the isql script into a file (just in
case you want/need to do some special processing with the file) or you
could directly manipulate the output with 'grep' and 'cut' and dump the
results into your bcp'able file. (I prefer to dump to a file so that I
can do some error checking before I do the 'grep' and 'cut'.)

So, a working example would look like:

================== begin shell example ===============

outfile=/tmp/$$.outfile
bcpfile=<whatever path/name you want to use>

# make sure you use a '-w' value that is wider than your
# expected row so as to eliminate unwanted CR/LF breaks

isql -U<user> -P<pwd> -S<server> -w1000 <<-EOF! > ${outfile} 2>&1
use DB2
go
select "GREP_ME:"+convert(varchar,int_col1)+"|"+
convert(varchar,date_col1,101)+"|"+
convert(varchar,char_col1)+"|"+
varchar_col1+":"
from whatever_table
go
EOF

... error checking on the contents of ${outfile} ?? ...

grep "GREP_ME:" ${outfile} | cut -d":" -f2 > ${bcpfile}

=================== end shell example ================

The select statement will generate ouput lines that look like:

GREP_ME:234|02/05/2005|abc|double:
GREP_ME:429|05/03/2005|xyz|triplewide:

The SQL makes sure everything is converted to character format (required
for the '+' to work ... and to insure all data is compacted on the same
line with no intervening white space).

The 'grep' has the effect of stripping out all non-data lines from
${outfile}.

The 'cut' will pull out everything between the ':'s. You want to wrap
the data in a different delimiter for 2 reasons ... a) makes it easier
for the 'cut' command and b) eliminates any white space at the front/end
of the data line.

So once the 'grep' and 'cut' have been run you end up with:

(I threw in the <CR>'s to represent no white space at the end of the
data lines.)

------------------- ${bcpfile} contents ------------
234|02/05/2005|abc|double<CR>
429|05/03/2005|xyz|triplewide<CR>
------------------- ${bcpfile} contents ------------

-----------------------------

If this chunk of code is to be used quite frequently (eg, 1000's of
times a day) as part of an application where speed and efficiency is of
utmost importance then you'll probably want to look at doing something
similar but with a compiled program (eg, C++, C#, VB, etc).

And obviously if you prefer (syb)perl, csh, awk, etc. ... knock yerself out.

Personally I prefer ksh because the majority of my clients (the folks
who'll have to maintain the code if/when I depart) find ksh to be less
'complicated' than (syb)perl/awk/C++/etc.

[Michael Peppler]

On Fri, 05 Aug 2005 08:27:02 -0700, VC wrote:

> Hi Dennis,
> That sounds amazing..... Thanks for your time and invaluable input.
>
> A couple of questions though,
> Do the local tables keep in sync with their "Master" tables when you do a
> "Select" on the local version or do I need to run any "Update/Refresh"
> periodically? or is this something that i do not have to worry.

The local (proxy) table is only a placeholder - there is no data in them.

So no refresh is necessary.

Michael
--
Michael Peppler [TeamSybase] mpep...@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html

[VC]

That is an awful lot of time that you have spent to explain the whole
process. Appreciate it very much Mark. I have gained a lot of insight from
the responses of others as well.

I think I should have communicated this clearly in advance. My apologies.
I am residing on a client box with read-write access to SybDB2 on
SybServer2. The source tables are on SybDB1 on SybServer1 with Read-Only
permissions.

Folks on SybDB1/SybServer1 won't create Views/TempTables. So I have to work
on SybServer2/SybDB2 and accomplish this task.

Component Integration Services, as recommended by Dennis might require
"Special Cooperation" from SybServer1/SybDB1 other than Ready-only access.
Remote chances of getting that done.

I will retain the ideas that you have shared for my other tasks and for
future. If I succeed in getting required permissions on SybServer1/SybDB1, I
will try to implement one of these there.

Thanks again for your invaluable time and input folks. Have a great weekend.
V!

"Mark A. Parsons" <iron_horse@no_spamola_compuserve.com> wrote in message

news:42f38e1e@forums-1-dub...

[Jeffrey R. Garbus]

Why not BCP out of Server 1 into Server 2, then modify the data as needed?

"VC" <lette...@yahoo.com> wrote in message news:42f372f4@forums-1-dub...

[dken...@caci.com]

V,

        Based on your questions below, here is what I do.  I have several situations.

1.  Data is available as it is from any table.  Just run the query.

2.  I have two J2EE developers that carry a copy of the test database with them on their laptops.  For them, I want to make sure that they have every change we make to the test database for development.  I have two scripts for them.  The first one drops and re-creates the proxy tables because you have to do that in order to get any structure changes.  It works like this.  Just drop and re-create the proxy table.

drop table E_[table]
go
create proxy_table E_[table]

EXTERNAL TABLE at "[remote server alias].[remote database name].dbo.[remote table name]"
go

3.  I also have scripts to update all the local data for the developers from the proxy table, which is very straightforward.  I drop the local table, recreate it so that I get any structure changes, and then update the data.

Drop table [table]
go
select * into [table] from E_[table]
go
grant select on [table] to GROUP
go

4.  Performance wise, I will tell you this.  I used to have to keep the local versions used by the developers up-to-date using bcp.  Using proxy tables in CIS takes one-tenth the time and once I put the CIS capability on their local databases, they can grab a last-minute change before departing in just minutes.  It's much, much less work.

Dennis

[VC]

Hi Dennis,

Thanks again for the invaluable time and tips. I have captured all these ideas in my personal notes/diary for my reference and sharing.

At this time, it is difficult to have the team on the other end to enable any permissions for CIS or creating views. For the current version, we are implementing a simple app that reads the data in an SQL-Dataset (C#) and writes to a BCP text file.

But this discussion has given me a lot of creative ideas from everyone and will be useful when I want to implement such solutions in our servers, database boxes that we have control on.

 

Thanks again for all the help, support and most importantly your time.

I will be dropping by with other questions and offer my time with any any answers I can ...

 

Have a great one,

V!

<dken...@caci.com> wrote in message news:42f64b4d$1@forums-2-dub...

[VC]

Hi Jeffrey,
That is one of the simplest approaches, but the tables that we want to join
are a bit bulky and would be nicer to avoid the traffic and space for 3
tables on a daily basis. If it were one time, we would do the BCP-out and
BCP-In..... But this is a daily task...

Cheers!!
V!
"Jeffrey R. Garbus" <je...@soaringeagle.biz> wrote in message
news:42f3ba1e$1@forums-1-dub...