CODEWITHSUNDEEP
sqlsql-serversql-insert
Ranjith Venkatesh
Ranjith Venkatesh

Reputation: 1362

Insert into table without values

I have a table with a primary key, 9 NOT NULL fields and 15 ALLOW NULL fields.

How do I insert a row in the table without having to explicitly set the values for the 9 fields?

Upvotes: 1

Views: 3473

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48934

Assuming that you have defaults created for those 9 NOT NULL fields, you have three options:

  • Don't set values for any field by using DEFAULT VALUES.

  • Don't set values for the NOT NULL fields by simply not specifying those fields in the INSERT column list or VALUES list.

  • Don't set values for the NOT NULL fields, assuming they are specified in the INSERT column list, by using the DEFAULT keyword instead of a value.

Example showing each option as well as the effect of explicitly inserting a NULL into a NULLable field that has a default:

DECLARE @Test TABLE
(
  ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  NotNullField DATETIME NOT NULL DEFAULT (GETDATE()),
  NullableFieldWithDefault DATETIME NULL DEFAULT (GETDATE()),
  NullableFieldWithNoDefault DATETIME NULL
);

-- No values specified for any fields
INSERT INTO @Test DEFAULT VALUES;

-- Values specified only for NULLable field with no default
INSERT INTO @Test (NullableFieldWithNoDefault) VALUES ('2112-12-21');

-- DEFAULT keyword used for fields with a default
INSERT INTO @Test (NotNullField, NullableFieldWithDefault, NullableFieldWithNoDefault)
VALUES (DEFAULT, DEFAULT, '1999-03-15');

-- NULLs specified for NULLable fields
INSERT INTO @Test (NullableFieldWithDefault, NullableFieldWithNoDefault)
VALUES (NULL, NULL);


SELECT * FROM @Test;

Results:

ID  NotNullField              NullableFieldWithDefault   NullableFieldWithNoDefault
--  -----------------------   ------------------------   --------------------------
1   2015-01-11 16:22:31.317   2015-01-11 16:22:31.317    NULL
2   2015-01-11 16:22:31.320   2015-01-11 16:22:31.320    2112-12-21 00:00:00.000
3   2015-01-11 16:22:31.320   2015-01-11 16:22:31.320    1999-03-15 00:00:00.000
4   2015-01-11 16:22:31.320   NULL                       NULL

Upvotes: 1

Related Questions