CODEWITHSUNDEEP
sql-serversql-server-2008-r2
zey
zey

Reputation: 6105

How to set identity column to created table in SQL server

I created a table in SQL Server like this:

CREATE TABLE [UserName]
(
   [ID] [int] NOT NULL ,
   [Name] [nvarchar] (50) NOT NULL ,
   [Address] [nvarchar] (200) NULL

   CONSTRAINT [PK_UserName] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

If I want to make ID an identity column, what do I need? Do I need to drop and create this table and set ID to [ID] [int] IDENTITY(1,1) NOT NULL .

By using drop and create, all of data from UserName table are lost .

Is there another way to set IDENTITY COLUMN to created table's column without losing data?

I use SQL Server 2008 R2 :)

Upvotes: 13

Views: 120340

Answers (4)

Sudeep A R
Sudeep A R

Reputation: 146

The easiest way:-

  1. Right click on the table in object explorer and select 'Design'

  2. Select the column for which you want to set identity and go to Column Properties

  3. Under 'Identity Specification' change '(Is Identity)' to 'Yes'

  4. Click Save.... Done :)

    enter image description here

Hope This Helps

Upvotes: 5

an phu
an phu

Reputation: 1823

Here is a solution that is minimally logged.

SELECT 
    IDENTITY(INT, 1,1) AS ID,
    Name, [Address]
INTO dbo.UserName_temp
FROM dbo.UserName;


ALTER TABLE dbo.UserName_temp
    ADD CONSTRAINT [PK_UserName] PRIMARY KEY CLUSTERED;

DROP TABLE dbo.UserName;

EXEC sp_rename 'dbo.UserName_temp', 'UserName';

SRC: http://sqlmag.com/sql-server/appending-identity-column-temporary-table

But the IDENTITY() function can "only in a SELECT statement with an INTO table clause."

http://msdn.microsoft.com/en-us/library/ms189838.aspx

Upvotes: 0

Devart
Devart

Reputation: 121902

Try this one -

DECLARE @temp TABLE
(
      ID INT NOT NULL 
    , Name NVARCHAR(50) NOT NULL 
    , [Address] NVARCHAR(200) NULL
) 

INSERT INTO @temp (ID, Name, [Address])
SELECT ID, Name, [Address]
FROM dbo.UserName

DROP TABLE dbo.UserName

CREATE TABLE dbo.UserName
(
      [ID] [int] IDENTITY(1,1) NOT NULL 
    , [Name] [nvarchar] (50) NOT NULL 
    , [Address] [nvarchar] (200) NULL
CONSTRAINT [PK_UserName] PRIMARY KEY CLUSTERED 
([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT dbo.UserName ON

INSERT INTO dbo.UserName (ID, Name, [Address])
SELECT ID, Name, [Address] 
FROM @temp

SET IDENTITY_INSERT dbo.UserName OFF

Upvotes: 8

user330315
user330315

Reputation: 

ALTER TABLE [UserName] DROP COLUMN [ID];

ALTER TABLE [UserName] 
    ADD [ID] integer identity not null;

Upvotes: 18

Related Questions