SQL Syntax : INSERT INTO when no columns to specify

[Andy <li...@ajsoft.net>]

Hi,

I couldn't find an answer in the manual so ask here. 
I have a table (for whatever reason) with just 1 column which is defined as 
"generated always as identity (start with 1)"

What is the syntax for an INSERT into such a table? I can't specify the column 
itself since it will be incremented by the datastore (or can I?)

I've tried 
INSERT INTO tbl () VALUES ()
and get 'encountered ")" at ...'

INSERT INTO tbl ()
and get 'encountered ")" at ...'

INSERT INTO tbl
and get 'encountered "<EOF"> at ...'

INSERT INTO tbl VALUES ()
and get 'encountered ")" at ...'


Thanks in advance
-- 
Andy

Re: SQL Syntax : INSERT INTO when no columns to specify

[Daniel John Debrunner <dj...@debrunners.com>]

Mamta Satoor wrote:

> I think this might be a bug in Derby where VALUES IDENTITY_VAL_LOCAL();
> returns null even after single row insert with values clause into table
> with identity column. A Jira entry would be good to keep track of this.

Already there.

http://issues.apache.org/jira/browse/DERBY-353

Dan.

Re: SQL Syntax : INSERT INTO when no columns to specify

[Mamta Satoor <ms...@gmail.com>]

I think this might be a bug in Derby where VALUES IDENTITY_VAL_LOCAL(); 
returns null even after single row insert with values clause into table with 
identity column. A Jira entry would be good to keep track of this.
 Mamta

 On 7/5/05, Andy Jefferson <li...@ajsoft.net> wrote: 
> 
> > If I'm understanding the question correctly, you should just use the
> > "DEFAULT" keyword to perform the insert:
> >
> > INSERT INTO tbl VALUES (DEFAULT)
> 
> Thanks Army.
> 
> That's certainly the sort of thing I'm looking for, *however*, if I do
> INSERT INTO tbl VALUES(DEFAULT);
> VALUES IDENTITY_VAL_LOCAL();
> 
> I get back null !
> 
> This goes against my interpretation of the documentation
> 
> http://incubator.apache.org/derby/docs/10.0/manuals/reference/sqlj82.html#HDRIDENTITYVALLOCAL
> since what I issues was a "single row INSERT statement", yet I get no 
> value
> back.
> 
> 
> If I change my table and add another column and then issue
> INSERT INTO tbl ("name") VALUES("new name")
> VALUES IDENTITY_VAL_LOCAL();
> I get back the value assigned to the identity column
> 
> 
> Is there a way I can do an INSERT AND access the value assigned by Derby ?
> 
> 
> --
> Andy
>

Re: SQL Syntax : INSERT INTO when no columns to specify

[Andy Jefferson <li...@ajsoft.net>]

> If I'm understanding the question correctly, you should just use the
> "DEFAULT" keyword to perform the insert:
>
> INSERT INTO tbl VALUES (DEFAULT)

Thanks Army. 

That's certainly the sort of thing I'm looking for, *however*, if I do
INSERT INTO tbl VALUES(DEFAULT);
VALUES IDENTITY_VAL_LOCAL();

I get back null ! 

This goes against my interpretation of the documentation
http://incubator.apache.org/derby/docs/10.0/manuals/reference/sqlj82.html#HDRIDENTITYVALLOCAL
since what I issues was a "single row INSERT statement", yet I get no value 
back.


If I change my table and add another column and then issue
INSERT INTO tbl ("name") VALUES("new name")
VALUES IDENTITY_VAL_LOCAL();
I get back the value assigned to the identity column


Is there a way I can do an INSERT AND access the value assigned by Derby ?


-- 
Andy

Re: SQL Syntax : INSERT INTO when no columns to specify

[Army <qo...@sbcglobal.net>]

Andy wrote:
> Hi,
> 
> I couldn't find an answer in the manual so ask here. 
> I have a table (for whatever reason) with just 1 column which is defined as 
> "generated always as identity (start with 1)"
> 
> What is the syntax for an INSERT into such a table?

If I'm understanding the question correctly, you should just use the "DEFAULT" 
keyword to perform the insert:

INSERT INTO tbl VALUES (DEFAULT)

For example,

ij> create table tbl (i int generated always as identity (start with 1));
0 rows inserted/updated/deleted
ij> insert into tbl values (default);
1 row inserted/updated/deleted
ij> select * from tbl;
I
-----------
1

1 row selected

Hope that helps,
Army