Insert id (autogenerated, only column)

问题:

How should my SQL-Statement (MS-SQL) look like if I wan't to insert a row into a table, which contains only one column with the autogenerated id?

Following two queries won't work:

INSERT INTO MyTable (MyTableId) VALUES (Null)
-- or simply
INSERT INTO MyTable 

Thx for any tipps. sl3dg3

回答1:

INSERT INTO MyTable (MyTableId) VALUES (Null) implicitly tries to insert a null into the identity column, so that won't work as identity columns may never be nullable and without a SET option you cannot insert an arbitrary value anyway, instead you can use:

INSERT INTO MyTable DEFAULT VALUES

回答2:

For completeness' sake, the SQL standard and most databases support the DEFAULT VALUES clause for this:

INSERT INTO MyTable DEFAULT VALUES;

This is supported in

• CUBRID
• Firebird
• H2
• HSQLDB
• Ingres
• PostgreSQL
• SQLite
• SQL Server (your database)
• Sybase SQL Anywhere

If the above is not supported, you can still write this statement as a workaround:

INSERT INTO MyTable (MyTableId)  VALUES (DEFAULT);

This will then also work with:

• Access
• DB2
• MariaDB
• MySQL
• Oracle

For more details, see this blog post here:

http://blog.jooq.org/2014/01/08/lesser-known-sql-features-default-values/