SQL Server: Inserting empty value (zero-arity INSERT)

Question

I made test table:

create table tmp (id BIGINT NOT NULL IDENTITY PRIMARY KEY)

Now I want to insert a value:

insert into tmp () values();

and it complains:

SQL Error [102] [S0001]: Incorrect syntax near ')'.

I cannot insert ID, because it fails with:

insert into tmp (id) values(1);
-- SQL Error [544] [S0001]: Cannot insert explicit value for identity column
-- in table 'tmp' when IDENTITY_INSERT is set to OFF.

DEFAULT syntax for VALUES is also forbidden:

insert into tmp values(default);
-- SQL Error [8101] [S0001]: An explicit value for the identity column in table 'tmp'
-- can only be specified when a column list is used and IDENTITY_INSERT is ON.

Sabin B · Accepted Answer · 2023-06-22T06:32:37.673

Try with

INSERT INTO tmp
DEFAULT VALUES;

To explicitly specify the column name,in particular cases, try with SET IDENTITY_INSERT ON
(1. and don't forget do set it back to off SET IDENTITY_INSERT tmp OFF )
(2. the value you provide for id column, because it's primary key, should be unique):

SET IDENTITY_INSERT tmp ON
INSERT INTO tmp(id)
VALUES(898989)
SET IDENTITY_INSERT tmp OFF

All Script :

create table tmp (id BIGINT NOT NULL IDENTITY PRIMARY KEY);
INSERT INTO tmp
DEFAULT VALUES;
SELECT *
FROM tmp;

id
1

dbfiddle

SQL Server: Inserting empty value (zero-arity INSERT)

1 Answers1