In Microsoft SQL database, I have a table where every column h开发者_运维技巧ave default values (either fixed values or stuff like identity or getdate()).

I am trying to write an SQL statement which will insert a new row in which every cell will have a default value.

Neither

insert into MySchema.MyTable

nor

insert into MySchema.MyTable () values ()

are valid syntax.

So is it possible to insert a new row without specifying any value?

insert into foo DEFAULT VALUES

Standard SQL

INSERT INTO tablename VALUES ( DEFAULT, DEFAULT, DEFAULT... )

When the DEFAULT reserved keyword is used in place of an actual value, it means the default value of the field. This allows you to specify some columns but not others.

The syntax is part of the SQL standard, and works on most databases since 2005:

• MS SQL TSQL - 2005+
• Oracle PL/SQL - 10g r2+
• MySQL - 5.5+/4.1+
• PostgreSQL - 8.0+
• DB2, H2, etc.

(Simple databases, such as SQLite or Access, tends to not support these "new" or "advanced" syntax.)

Non-Standard

INSERT INTO tablename DEFAULT VALUES

This will insert a new row populating everything with default values.

As far as I know this syntax is non-standard, and few databases support it - I know only MS SQL and PostgreSQL. While I personally don't use it, I can see the use case and it may be made standard like DEFAULT fields in the future.