0

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.
gavenkoa
  • 509
  • 2
  • 8
  • 22
  • Why do you want to do this? Is the tmp table supposed to be used as some sort of counter?...because there already are objects that exist to be used as counters, such as Sequences. – J.D. Jun 22 '23 at 12:28
  • But see also https://stackoverflow.com/a/67808746/14868997 – Charlieface Jun 22 '23 at 13:05
  • @J.D. It is more a theoretical question - I tested alter ... default 0 not null and needed the simplest temporary table signature. – gavenkoa Jun 22 '23 at 13:09
  • @Charlieface First reference answers the question... I didn't know IDENTITY is something special... – gavenkoa Jun 22 '23 at 13:10
  • 1
    IDENTITY isn't special, it's just another type of DEFAULT constraint. The same thing can be done with any table with defaults on all columns (even NULL as the default). – Charlieface Jun 22 '23 at 13:31

1 Answers1

3

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

Sabin B
  • 4,401
  • 1
  • 18
  • 24