CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Kaja
Kaja

Reputation: 3057

Alter table to set the IDENTITY to off

I have a table, which has a column(orderid), which its IDENTITY is set to true. Now I would like to set it off. How can I do that with ALTER COLUMN?some thing like this?

ALTER TABLE MyTable
ALTER Column MyColumn SET IDENTITY OFF

Upvotes: 9

Views: 59538

Answers (3)

Abdul Hannan Ijaz
Abdul Hannan Ijaz

Reputation: 844

You can do this in 4 steps

  1. Make new Column
  2. Copy Data to that Column
  3. Remove old column
  4. Rename new column to old one

Upvotes: 5

StackUser
StackUser

Reputation: 5398

You have to use SET IDENTITY_INSERT TO ON. If you set it as ON then you should explicitly pass values to the ID Column.

Why should you switch off the Identity? May be you are trying to pass some explicit values.

Please refer the sample demo here.

-- Create tool table.
CREATE TABLE dbo.Tool
  (
     ID   INT IDENTITY NOT NULL PRIMARY KEY,
     NAME VARCHAR(40) NOT NULL
  );

GO

-- Inserting values into products table.
INSERT INTO dbo.Tool
            (NAME)
VALUES      ('Screwdriver'),
            ('Hammer'),
            ('Saw'),
            ('Shovel');

GO

-- Create a gap in the identity values.
DELETE dbo.Tool
WHERE  NAME = 'Saw';

GO

SELECT *
FROM   dbo.Tool;

GO

-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool
            (ID,
             NAME)
VALUES      (3,
             'Garden shovel');

GO

-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON;

GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool
            (ID,
             NAME)
VALUES      (3,
             'Garden shovel');

GO

SELECT *
FROM   dbo.Tool;

GO

-- Drop products table.
DROP TABLE dbo.Tool;

GO

Upvotes: 7

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

Once the identity column is set you cannot remove it or you cannot set it to OFF.

You probably have to drop the column by first copying the data into some other column(which does not have the identity). So it would be like add a new column to your table and copy the values of your existing identity column to it. Then drop the old column(having identity) and finally rename the new column to the old column name.

Upvotes: 8

Related Questions