javaDB IDENTITY column and INSERTS

[myraddin]

Hi all.
My first post so bare with me if I have chosen the wrong place to ask.

I created a table in JavaDB like this:

CREATE TABLE DIRECTION(
  DIRECTION_ID INT PRIMARY KEY NOT NULL GENERATED DEFAULT AS IDENTITY, 
  SOURCE_AREA_ID      INT NOT NULL
);

(it's a bit shortened but will prove my point.

Now I would like to throw in some data by using INSERT with a SELECT query like this:

INSERT INTO DIRECTION 
   SELECT AREA_ID
    FROM FOOBAR_AREA;

The error thrown is (as expected): The number of values assigned is not the same as the number of specified or implied columns.

Then I try:

INSERT INTO DIRECTION 
   SELECT {auto|default|xxx?},AREA_ID
    FROM FOOBAR_AREA;

and nothing helps.

How DOES one do an INSERT using select query into a table with an auto-generated identity column ?

Thanks !

[Webuser]

First wellcome :)

I am afraid it is not Java question but SQL one :(

Anyway... If your table contains X columns you have to INSERT X params. Either they would be null or not it doesn't matter...

[myraddin]

Thank you Webuser.

I saw a few posts about JavaDB on these forums so ... i thought...

Never mind then.

As for you reply it's not actually related to my question but thank you for trying ;)

Happy coding.

[masijade]

Have you tried simply

INSERT INTO DIRECTION (SOURCE_AREA_ID)
   SELECT AREA_ID
    FROM FOOBAR_AREA;

Seeing as how when you only give it one value when the table has two columns, the DB is not going to take responsibility for the action (and consequences) and attempt to "guess" at which column should be getting the value provided.