13

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.

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?

Arseni Mourzenko
  • 50,338
  • 35
  • 112
  • 199

2 Answers2

25

insert into foo DEFAULT VALUES

Donnie
  • 45,732
  • 10
  • 64
  • 86
0

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:

(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.

Sheepy
  • 17,324
  • 4
  • 45
  • 69
  • This does not work in TSQL (i.e. Microsoft SQL Server). "Column name or number of supplied values does not match table definition." – Reversed Engineer May 02 '18 at 13:50
  • @DaveBoltman Can't believe I made an answer that was so simple. I took some time to test to make sure the syntax works, and it is indeed standard, you just need to specify it for every field that you want default of. I've updated the answer and linked to syntax documentations. – Sheepy May 07 '18 at 03:51