24

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.

0

3 Answers 3

33

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
1
  • 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
    Jan 9, 2018 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 ;
0

here is the solution

Insert into TableName Default values;
select Scope_identity();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.