INSERT a row only with default or null values

Question

I have a table with a varying amount of columns of varying names.

All columns are one of the following:

• identity columns,
• NULL columns,
• or have a default value

Now I want to insert into this table a new row and read its content.

I tried all the following:

INSERT INTO globalsettings() VALUES()
INSERT INTO globalsettings VALUES()
INSERT INTO globalsettings VALUES
INSERT INTO globalsettings

Did I miss the correct syntax, or won't I be able to insert an all-defaults row?

Answer 1

INSERT INTO globalsettings DEFAULT VALUES;

you can find the description here: http://msdn.microsoft.com/en-us/library/ms174335.aspx

Answer 2

You could do something like this:

INSERT INTO globalsettings (Column1) VALUES (DEFAULT)

It will use the default value for Column1 and all other columns.

Answer 3

The number of columns in the table must equal the number of values when if you decide not to use the column names in the statement. For example, if you have 4 columns where first is the identity, second and third are nullable, and fourth is a default int 0.

You can do

INSERT INTO globalSettings DEFAULT VALUES

OR

You can specify all values:

INSERT INTO globalSettings Values (NULL, NULL, 0)

OR

You can specify the columns and have the rest to the default null or 0.

INSERT INTO globalSettings(secondColumn) VALUES (Default)

OR

  INSERT INTO globalSettings(secondColumn) VALUES (null)

This will insert a row with 1,null,null,0

You can not insert into a table without specifying what you want to insert.