How to insert a record with only default values?

user_7850 0
Viewed 16005

If I have an SQL table with all default columns (e.g. identity column + any number of columns all with default values), what is the SQL statement to insert a row with no explicit values given?

insert MyTable /* ( doh, no fields! ) */ 
-- values( doh, no values! )

What's the trick?

sql-server
3 Answers

The accepted answer only works for one row, not for multiple rows.

Let us assume you know how many rows to insert, but you want all default values. You cannot do the following, for instance

INSERT MyTable
SELECT DEFAULT VALUES  -- Incorrect syntax near the keyword 'DEFAULT'.
FROM SomeQueryOrView;
-- or
INSERT MyTable
DEFAULT VALUES  -- Incorrect syntax near the keyword 'FROM'.
FROM SomeQueryOrView;

Instead we can hack MERGE to do this

MERGE INTO myTable
USING (SELECT SomeValue FROM SomeQueryOrView) s
ON 1 = 0  -- never match
WHEN NOT MATCHED THEN
    INSERT DEFAULT VALUES;

A bonus benefit is that we can OUTPUT data from columns which are not being inserted:

MERGE INTO myTable
USING (SELECT SomeValue FROM SomeQueryOrView) s
ON 1 = 0  -- never match
WHEN NOT MATCHED THEN
    INSERT DEFAULT VALUES
OUTPUT inserted.Id, s.SomeValue;
user_148689970
Related