1

I have the following table:

CREATE TABLE [dbo].[Subject] (
    [SubjectId]    INT            IDENTITY (1, 1) NOT NULL,
    [Name]         NVARCHAR (50)  NOT NULL,
    [Version]      ROWVERSION     NOT NULL,
    [CreatedBy]    NVARCHAR (128) NOT NULL,
    [CreatedDate]  DATETIME       NOT NULL,
    [ModifiedBy]   NVARCHAR (128) NOT NULL,
    [ModifiedDate] DATETIME       NOT NULL,
    CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED ([SubjectId] ASC)
);

I would like to create a new row that is a copy of a row with SubjectId 2 but I want the new row to have a SubjectId of 0. Is there a way that I can turn the identity off and insert the new row as a copy of the row with SubjectId 2. Note that I don't want to turn identity on again.

Following Jen's answer now I have:

SET IDENTITY_INSERT Subject OFF
insert into Subject select 0,Name,version, createdby,createddate,modifiedby,modifiedDate from subject where subjectid=2

Which gives another error:

Msg 8101, Level 16, State 1, Line 4 An explicit value for the identity column in table 'Subject' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Improve this question
4
  • I don't get it. If you want to insert explicit values into the identity column of a table , you need to Set Identity_insert off. But you say you don't want turn identity on again, then why do you need an identity column at all ? Commented Jun 15, 2014 at 11:33
  • I don't need an identity column any more. I want to turn it off but I am not sure how to do that. Commented Jun 15, 2014 at 11:34
  • 1
    The error message is clear: "An explicit value for the identity column in table 'Subject' can only be specified when a column list is used and IDENTITY_INSERT is ON." Commented Jun 15, 2014 at 11:48
  • 1
    You should never leave IDENTITY_INSERT in the ON position. It should only be turned off for short periods of time. Commented Jun 15, 2014 at 12:10

3 Answers 3

Reset to default
6

SET IDENTITY_INSERT Subject ON means you can explicitly insert values into an identity column in Subject table.

Therefore if you want to insert values explicitly into an identity column you will TURN ON this feature and once you have added the value into identity column you will need to turn it OFF .

Note

Also when you have Identity_Insert on you have to mention the column names in your insert statement explicitly.

Something like this....

SET IDENTITY_INSERT Subject ON;

INSERT INTO Subject  (subjectid,Name,version, createdby,createddate,modifiedby,modifiedDate)
SELECT 0,Name,version, createdby,createddate,modifiedby,modifiedDate 
FROM subject 
WERE subjectid = 2

SET IDENTITY_INSERT Subject OFF;

One more Important thing. Since you are inserting values explicitly into your identity column, not in this particular case but when you insert a value that has not been generated by the Identity column yet, later on when you have identity insert off and you expect identity column to generate values for you, and if you have a Unique Index or a Primary key constraint on that column, Identity column will generate values that you might have already inserted explicitly, which will result in duplicates in your primary key column(obviously sql server wont let this happen) and you will get an error something like... "Duplicate value in Primary key column".

To avoid this you can reseed you identity column to skip these values you have inserted explicitly by doing what is shown below after your explicit inserts in identity column.

DBCC CHECKIDENT(Table_Name, RESEED, 0)   --<-- some smallest value
DBCC CHECKIDENT(Table_Name, RESEED)      --<-- without any seed value

This will reset the identity column to next highest available identity value.

Most Important Note

If it is an Identity value, leave it alone, dont mess with it, let it generate values for you, If you do need to influence the values being generated by identity column, Do not have an identity column, make it a simple INT column.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

If you want to remove the identity property from the column, you can do it by a cumbersome process of:

  1. Inserting a new column
  2. Updating its value
  3. Dropping the original column

Then, if you want, you can basically repeat to get the original column name back. Something like this:

alter table Subject add SubjectId_new int;

update Subject set SubjectId_new = SubjectId;

alter table Subject drop SubjectId;

And then perhaps:

alter table Subject add SubjectId int;

update Subject set SubjectId = SubjectId_new;

alter table Subject drop SubjectId;

Or, just copy the data into a new table with the format that you want.

Improve this answer

Comments

1

Switch ON the Identity Insert to allow explicitly insert into the identity column of a table

SET IDENTITY_INSERT [dbo].[Subject] ON

Copy the record

insert into [dbo].[Subject]  (subjectid,Name,version, createdby,createddate,modifiedby,modifiedDate) (select 0,Name,version, createdby,createddate,modifyby,nodifiedDate) from [dbo].[subject] where subjectid=2

Restore Identity Insert

SET IDENTITY_INSERT [dbo].[Subject] OFF
Improve this answer

5 Comments

I tried and I get this error message: Incorrect syntax near the keyword 'IDENTITY_INSERT'.
I updated my answer to show what the latest is. I had to change the syntax a little to make it stop showing syntax error message before running it.Thanks
@SamanthaJ I have updated my answer. Try it with the column names in the insert statement.
@Jens . . . I think the logic is backwards. First you want to set it to ON and then to OFF.
@GordonLinoff Yes you are right. I have changed my post. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.