Take the 2-minute tour ×
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.

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 '13 at 17:36
    
I'm testing something and only need the IDENTITY column. It's not for production. –  Apocatastasis Apr 25 '13 at 17:45
2  
@thursdaysgeek plenty of use cases. For example, a sequence generator. What other columns do you need? –  Aaron Bertrand Apr 25 '13 at 18:56

3 Answers 3

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 '13 at 16:16
2  
I prefer explicitely stating the columns: INSERT INTO the_table (Id) VALUES (default); –  a_horse_with_no_name Apr 26 '13 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 '13 at 13:17

here is the solution

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

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

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.