Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

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)
share|improve this question
Ok, but why? Where would you use a table with just an identity column? – thursdaysgeek Apr 25 at 17:36
I'm testing something and only need the IDENTITY column. It's not for production. – Apocatastasis Apr 25 at 17:45
2  
@thursdaysgeek plenty of use cases. For example, a sequence generator. What other columns do you need? – Aaron Bertrand♦ Apr 25 at 18:56

3 Answers

up vote 10 down vote accepted 
INSERT dbo.TABLE DEFAULT VALUES;

In addition:

  1. always use the schema prefix
  2. always terminate statements with semi-colons
share|improve this answer
Thanks for the additional advise. – Apocatastasis Apr 25 at 16:16
2  
I prefer explicitely stating the columns: INSERT INTO the_table (Id) VALUES (default); – a_horse_with_no_name Apr 26 at 7:35
@a_horse_with_no_name sure, but if you have 15 columns and they all have default values, is it worth the hassle? Regardless of the number of columns I think this is pretty much an edge case anyway... – Aaron Bertrand♦ Apr 26 at 13:17
up vote 3 down vote

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 ;
share|improve this answer
up vote 0 down vote

here is the solution 

Insert into TableName Default values;
select Scope_identity();
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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