28

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,778
  • 13
  • 102
  • 151
Apocatastasis
  • 615
  • 2
  • 7
  • 15

3 Answers3

39

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
  • 181,950
  • 28
  • 405
  • 624
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ᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
Kahn
  • 1,803
  • 2
  • 20
  • 28
0

here is the solution 

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