In T-SQL, how to insert a new row with all values set by default?
In Microsoft SQL database, I have a table where every column have 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.
insert into MySchema.MyTable
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
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:
(Simple databases, such as SQLite or Access, tends to not support these "new" or "advanced" syntax.)
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.