CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32828

How can I turn off identity and insert into a row in SQL Server 2012?

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.

Upvotes: 1

Views: 4752

Answers (3)

Jens
Jens

Reputation: 69470

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

Upvotes: 1

M.Ali
M.Ali

Reputation: 69564

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.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 2

Related Questions