SQL Anywhere database - select * but....

Author

select * but....

Steve

2005-07-25, 9:24 am

I'm using a select - insert command in SQL Anywhere 9.

I need everything from a column except its primary key,
which I will have to create a unique in its place. I do not
want to type out all of the column names except the PK
column for each table I need.

I'm looking for a nice trick (if there is one) where I can
use select * from table, but exclude this one column.

Reg Domaratzki \(iAnywhere Solutions\)

2005-07-25, 11:23 am

You could query the system tables and build up your own SQL statement in a
stored procedure. The following SP simply messages the SQL to the console,
but you could do a EXECUTE IMMEDIATE on the string just as easily. The
table also assumes that there are no tables names that are duplicates, but
owned by different users.

procedure Build_SQL ( in @source_table varchar(128), in @target_table
varchar(128) )
begin
declare source_columns long varchar;
declare target_columns long varchar;

select list(column_name)
into source_columns
from sys.syscolumn
where table_id = (select table_id from sys.systable where table_name =
@source_table )
and pkey = 'N';

select list(column_name)
into target_columns
from sys.syscolumn
where table_id = (select table_id from sys.systable where table_name =
@target_table )
and pkey = 'N';

message 'insert into ' || @target_table || ' (' || target_columns || ' )
select ' || source_columns || ' from ' || @source_table;
end

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<Steve> wrote in message news:42e4fd01.1254.1681692777@sybase.com...
> I'm using a select - insert command in SQL Anywhere 9.
>
> I need everything from a column except its primary key,
> which I will have to create a unique in its place. I do not
> want to type out all of the column names except the PK
> column for each table I need.
>
> I'm looking for a nice trick (if there is one) where I can
> use select * from table, but exclude this one column.

Shuchit

2005-07-25, 8:25 pm

Steve wrote in news:42e4fd01.1254.1681692777@sybase.com:

> I need everything from a column except its primary key,
> which I will have to create a unique in its place. I do not
> want to type out all of the column names except the PK
> column for each table I need.
>
> I'm looking for a nice trick (if there is one) where I can
> use select * from table, but exclude this one column.
>

You could use Query Editor which is part of dbisql. It will let you build the
column list by just pointing at the columns you want rather than having to
type out all the columns yourself.

Shuchit

anil k goel

2005-07-26, 9:23 am

You may be able to make use of the "WITH AUTO NANE" clause.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

<Steve> wrote in message news:42e4fd01.1254.1681692777@sybase.com...
> I'm using a select - insert command in SQL Anywhere 9.
>
> I need everything from a column except its primary key,
> which I will have to create a unique in its place. I do not
> want to type out all of the column names except the PK
> column for each table I need.
>
> I'm looking for a nice trick (if there is one) where I can
> use select * from table, but exclude this one column.

Sponsored Links