Re: Sybase generic bcp HELP identity columns

On Feb 27, 12:43 pm, tpre...@xxxxxxx wrote:
Hello

I have Sybase 12.5.3 on Solaris 5.8 and I need a generic bcp out and
then in for all tables, but I have 3 tables which have identity
columns that I need to keep.
Also in the same script I want to leave out 3 tables so I have a
select out and then a like not '%MY_TABLE%' but how can I suppress
these tables with IDENTITY column?
I would like to avoid to hard cod all the tables if possible.

Thank you
Teresa

I *think* I understand what you're talking about --

You want a shell script, yes?

Within said script contains code such as:
$path_for/isql -Uuser -Ppassword -Ddatabase <<END > /tmp/dat1
select name, ' ' as tossout from sysobjects
where uid = 1
and type = 'U'
and name not like '%MY_TABLE%'
go
END
awk 'NF == 1 {print $1}' < /tmp/dat1 > /tmp/dat2

OK, at this point file /tmp/dat2 contains all of the tables you want.
Some tweakage may be necessary, such as you happen to want tables
owned by somebody other than dbo (uid = 1). The select statement is
specially designed so that every row with just a table name has
exactly one column, and the "awk" command only prints rows with
exactly one column. So that throws away headers and "(N rows
affected)" etc.

So anyhow, additional scripting (Bourne shell or Ksh) can read:

for table in `cat /tmp/dat2`
do
bcp database.dbo.$table out $table.bcp -c -Uuser etc.
done

Again -- necessary tweakage if "dbo" is not what you're looking for.
Note the "reverse quote" around the "cat /tmp/dat2".

Also, I shouldn't have to say it, but just in case I *do*, replace the
words "user", "password", "database", etc., with whatever is actually
appropriate for your system! (I.e, don't try to cut-n-paste the
posting into your script, it won't work!)

For additional help on the system tables, see:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc36274_1251/html/tables/title.htm&toc=/com.sybase.help.ase_12.5.1/toc.xml

Hope that's helpful.

.