SQL Anywhere database - Insert ... Select

Author

Insert ... Select

TPS

2005-07-21, 8:23 pm

Is there some syntax to get this to work

insert into table_A (select ___,100,B,C,D from perform.table_A where A =
1);

the ___ is the primary key for table_A which is a COUNT_A with default
autoincrement.

for a single insert this would work

insert into table_A values(default,100,B
,C,D);

Question: Is there some syntax that would allow the default autoincrement to
function in a insert into (select...)

Thanks

TPS

Graeme Perrow

2005-07-21, 8:23 pm

You didn't mention what version of ASA you're using, but if it's 8.0.2
or later, you can use the WITH AUTO NAME clause:

insert into table_A with auto name (select 100 as <col_name>,B as
<col_name>,C as <col_name>,D as <col_name> from perform.table_A where A
= 1);

Replace <col_name> with the name of the column in table_A where you want
to the data to go. Other columns will be filled in with their default
values.

TPS wrote:
> Is there some syntax to get this to work
>
> insert into table_A (select ___,100,B,C,D from perform.table_A where A =
> 1);
>
> the ___ is the primary key for table_A which is a COUNT_A with default
> autoincrement.
>
> for a single insert this would work
>
> insert into table_A values(default,100,B
,C,D);
>
> Question: Is there some syntax that would allow the default autoincrement to
> function in a insert into (select...)

--
Graeme Perrow
Senior Software Developer
gperrow _at_ ianywhere _dot_ com
iAnywhere Solutions Inc.
A Sybase company

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

Breck Carter [TeamSybase]

2005-07-22, 7:23 am

Code an explicit name list after the INSERT keyword, and omit COUNT_A
from both the name list and the SELECT. Let us assume the second
column is called X:

INSERT table_A ( X, B, C, D ) SELECT 100, B, C, D FROM ...

Breck

On 21 Jul 2005 15:43:31 -0700, "TPS" <ts@scientexlc.com> wrote:

>Is there some syntax to get this to work
>
>insert into table_A (select ___,100,B,C,D from perform.table_A where A =
>1);
>
>the ___ is the primary key for table_A which is a COUNT_A with default
>autoincrement.
>
>for a single insert this would work
>
>insert into table_A values(default,100,B
,C,D);
>
>Question: Is there some syntax that would allow the default autoincrement to
>function in a insert into (select...)
>
>Thanks
>
>TPS
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links