Modify a column in a table to auto-increment.

Modify a column in a table to auto-increment.

Yesterday, a student asked me, "If a table has been created, can I change one field to auto-increment ?", "Yes, but there is no need to modify it. We should design it when creating a table," I said. At this time, he and another student
Discuss it. He thought it was okay. The other one tried and said no. Because they are not students with my class and they have consulted their teachers, I have no further comments.

Requirements:
How to change a column in a table to auto-increment.

Answer:
1) scenario 1: The table has no data. You can use drop column and then add column.

Alter table Name drop column name
Alter table name add column name int identity (1, 1)

2) Scenario 2: Some data already exists in the table.

********************/ -- Determine whether the test table if object_id (N 'test ', N 'U') is not nulldrop table test -- create test table create table test (id int not null, name varchar (20) not null) -- insert temporary data insert into test values (1, 'Jackie Chan ') insert into test values (3, 'zhang Ziyi') insert into test values (4, 'Liu ruoying ') insert into test values (8, 'faye W ') select * from test/**************** to change the auto-growth column ************** * *****/begin transactioncreate table test_tmp (id int not null identity ), name varchar (20) not null) goset identity_insert test_tmp ongoif exists (select * from test) exec ('insert into test_tmp (id, name) select id, name from test with (holdlock tablockx) ') goset identity_insert test_tmp offgodrop table testgoexec sp_rename N 'test _ tmp', N 'test ', 'object' gocommitGO ***************** /insert into test values ('zhang man ') select * from test

Conclusion: It is easiest to modify the table design interface. If the existing data in this column is stored, modification may cause an exception. You can use the data import and export method to solve the problem. In short, no matter what method is used, you need to back up the data in advance.

How can I change a field of a table to auto-increment column in an SQL statement?

Select auto-incrementing column = identity (int, 1, 1), * into # tb from tableName
Drop table tabelNameselect * into tableName from # tbdrop table # tb you can directly modify the table structure in the database and add a column (that is, the column with increasing content ), set this column as the ID column and auto increment by 1. You can save it in sql2000, but it doesn't feel good... if the table has more relationships, we do not recommend using if exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [p_setid] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [p_setid]
GO
-- Convert a field in the table into an ID field and retain the original value.
-- Note: To delete the original table, if the table is associated with other tables, the association must be re-created.
-- Call example
Exec p_setid 'table name', 'field name to be converted'
--*/
Create proc P_SETID
@ Tbname sysname, -- Name of the table to be processed
@ Fdname sysname -- the field name to be converted to the ID field
As
Declare @ s1 varchar (8000), @ s2 varchar (8000), @ tmptb sysname
Select @ s1 = '', @ s2 ='', @ tmptb = '[tmp _' + @ tbname + '_ bak]'
Select @ s1 = @ s1 + ', [' + name + ']'
+ Case name when @ fdname then '= identity (bigint, 1, 1) 'else' end
, @ S2 = @ s2 + ', [' + name + ']'
From syscolumns where object_id (@ tbname) = id
Select @ s1 = substring (@ s1, 2,8000), @ s2 = substring (@ s2, 2,8000)
Exec ('select top 0' + @ s1 + 'into '+ @ tmptb +' from ['+ @ tbname +']
Set identity_insert '+ @ tmptb +' on
Insert into '+ @ tmptb +' ('+ @ s2 +') select '+ @ s2 +' from ['+ @ tbname +']
Set identity_insert '+ @ tmptb +' off
')
Exec ('drop table ['+ @ tbname +'] ')
Exec sp_rename @ tmptb, @ tbname
Go

-- Use test

-- Create a test table
Create table (bigint, varchar (10 ))
Insert into table
Select 1, 'zhang san'
Union ...... remaining full text>
 
How can I change a field of a table to auto-increment column in an SQL statement?

Should there be no data for the auto-increment column? Otherwise, you cannot change it. I think it's easy to delete and add more, hehe alter table aaa drop column B
Alter table aaa add B int identity (1, 1)