Insert with union

[alexhu]

I am trying to do this (the real code is a bit large - so for brevity....)

insert into <table>
select a.column, b.column
from table_a table_b
where <statement>
union
select a.column, b.column
from table_a table_b
where <statement>

and I get the error

ERROR at line 1: <table>
ORA-00947: not enough values

Can I do this or not ?

Alex

 

[sem]

This may mean that your <table> contains more than 2 fields. I also can not find neither a nor b aliases in your statement.

Regards, Dima

 

[alexhu]

yes it does (23 columns per statement)

I take it thats a no-no then ?

Alex

 

[avjoshi]

Obviously the table you are inserting into has more number of columns than your select statement. You may want to specify the columns you want to insert into.

Try this insted,

insert into <table> (col1,col2)
select a.column, b.column
from table_a table_b
where <statement>
union
select a.column, b.column
from table_a table_b
where <statement>

 

[alexhu]

Ok I have it now thanks

Alex