How to do ALTER TABLE in SQL Server 2008 R2 to add IDENTITY (1,1)? [closed]

Question

How to do ALTER TABLE in SQL Server 2008 R2 to add IDENTITY (1,1)? [closed]

Navigation

#1 by (1 votes)

0

How do I add IDENTITY (1,1) to Table Query # 1 , in the UserUID column? > whose Table has pre-existing UserUID records without having to corrupt (Re-create the table) or delete the data from the pre-existing UserUID column?

IMPORTANT: I already know that it is self-reinforcing and unique, at least I think that was understood by (IDENTITY (1,1))

CONSULTATION Nº1

USE [PS_UserData]
GO

/****** Object:  Table [dbo].[Users_Master]    Script Date: 07/21/2018 12:43:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

    CREATE TABLE [dbo].[Users_Master](
        [RowID] [int] NULL,
        [UserUID] [int] NULL,
        [UserID] [varchar](max) NOT NULL,
        [Pw] [varchar](max) NOT NULL,
        [JoinDate] [smalldatetime] NOT NULL,
        [Admin] [bit] NOT NULL,
        [AdminLevel] [tinyint] NOT NULL,
        [UseQueue] [bit] NOT NULL,
        [Status] [smallint] NOT NULL,
        [Leave] [tinyint] NOT NULL,
        [LeaveDate] [smalldatetime] NULL,
        [UserType] [char](1) NOT NULL,
        [UserIp] [varchar](15) NULL,
        [ModiIp] [varchar](15) NULL,
        [ModiDate] [datetime] NULL,
        [Point] [int] NOT NULL,
        [Enpassword] [char](32) NULL,
        [Birth] [varchar](8) NULL,
        [email] [varchar](max) NULL,
        [Activation] [varchar](max) NULL,
        [SocioRock] [varchar](max) NULL,
        [nacimiento] [varchar](max) NULL,
        [profile_img] [varchar](max) NULL,
        [nombre.] [varchar](max) NULL,
        [apellido.] [varchar](max) NULL,
        [firmas] [varchar](max) NULL,
        [pais] [varchar](255) NULL,
        [mes] [int] NULL,
        [dia] [int] NULL,
        [anio] [int] NULL,
        [nombre] [varchar](255) NULL,
        [apellido] [varchar](255) NULL,
        [MotivoBan] [varchar](255) NULL,
        [GS] [int] NULL,
        [Contador] [tinyint] NULL,
        [Activo] [varchar](255) NULL,
        [FechaDuper] [datetime] NULL,
        [FinBan] [varchar](255) NULL,
        [Verifi] [int] NULL,
        [Protector] [varchar](40) NULL,
        [Staff] [int] NULL,
        [DEV] [int] NULL,
        [Email_viejo] [varchar](255) NULL,
        [UserID_Viejo] [varchar](255) NULL,
        [Point2] [int] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

sql sql-server

asked by Juan Carlos Villamizar Alvarez 21.07.2018 в 04:14

source

1 answer

Switch to function typescript html code Permissions for specific bucket and specific folder

score 1 · Answer 1

a column can not be done identity after it is created, you will have to create a new column

 Alter Table Names
 Add Id_new Int Identity(1, 1)
 Go

This will generate the identities, but unfortunately it may not coincide with the values you had before.

If you want the identitys to agree with another column you will have to re-insert all the records with the identity that corresponds to them.

for that you need to enable identity insert

 SET IDENTITY_INSERT YourTable ON
 GO

and then insert each record (taking into account the identity) and then delete the old record.

and finally disable identity insert

 SET IDENTITY_INSERT YourTable OFF

Once the inserts you will have to regenerate the seed (the point where they will begin to generate the identities again

 DBCC CHECKIDENT ('Emp', RESEED, 1)

final comment ... all this is easier to do if you create a new table