27

Given a table with only an IDENTITY column, how do you insert a new row? I've tried the following:

INSERT INTO TABLE
(Syntax error)

INSERT INTO TABLE VALUES()
(Syntax error)

INSERT INTO TABLE (Id) VALUES()
(Syntax error)

I am testing something and only need the IDENTITY column. It's not for production. Otherwise, such a table can be used as a sequence generator, where no other columns are needed.

András Váczi
  • 31,278
  • 13
  • 101
  • 147
Apocatastasis
  • 605
  • 2
  • 7
  • 15

3 Answers3

37

From the documentation:

DEFAULT VALUES
Forces the new row to contain the default values defined for each column.

So:

INSERT dbo.TABLE DEFAULT VALUES;

In addition:

  1. always use the schema prefix
  2. always terminate statements with semi-colons
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • Interesting, it appears to be part of SQL2003 (F222) but I have never seen the construction before ( INSERT INTO appears to be required though ). You learn something new every day :-) – Lennart - Slava Ukraini Jan 09 '18 at 19:47
3

Another way would be to use IDENTITY_INSERT. That way you can manually define which values you want to put in. Like so:

SET IDENTITY_INSERT TABLE ON ;

INSERT INTO TABLE (ID) VALUES (1), (2) ;

SET IDENTITY_INSERT TABLE OFF ;
ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Kahn
  • 1,813
  • 1
  • 20
  • 28
0

here is the solution 

Insert into TableName Default values;
select Scope_identity();
Muhammad Jawad
  • 99
  • 1
  • 1
  • 5