mutiple inserts

[Guest_imported]

Hi
I am trying to insert multiple rows in a table( say table A) after select some of the fields from another table (say Table B) .But Table A has a primary key .

i can i use the format
insert into A
select x,y,z from B

I tried
insert into A
select (select max(id)+1 from A),y,z from B
but it doesn't work as probably it queries and sotres the resultset.
please help,
this is very urgent and imp for me.

appreciate ur help,
thanks in advance,
Amit

 

[grega]

I assume by this your primary key is a uniquely generated number. How is the number generated?, i.e. is it maintained by the database or maintained by the programs that use the table?

If the column is maintained by the database (e.g. for Sybase it will be type identity, for other db's type serial, etc.), then you may not have to specify a value at all, so something like this would work. This assumes A has 3 columns, where column1 is a serial/identity column.

insert into A
select y,z from B

Some DBs require that you specify a value of 0 for a serial column ... it depends on what DB you are using.

If the primary key column in A is not maintained by the DB, it probably should be. In this case it is difficult to do multiple insert from a single SQL statement ... and probably some form of cursor would be a better bet.

Greg.