SQL Server, How to set auto increment after creating a table without data loss?
I have a table table1 in SQL server 2008 and it has records in it.
I want the primary key table1_Sno column to be an auto-incrementing column. Can this be done without any data transfer or cloning of table?
I know that I can use ALTER TABLE to add an auto-increment column, but can I simply add the AUTO_INCREMENT option to an existing column that is the primary key?
Changing the IDENTITY property is really a metadata only change. But to update the metadata directly requires starting the instance in single user mode and messing around with some columns in sys.syscolpars and is undocumented/unsupported and not something I would recommend or will give any additional details about.
For people coming across this answer on SQL Server 2012+ by far the easiest way of achieving this result of an auto incrementing column would be to create a SEQUENCE object and set the next value for seq as the column default.
Alternatively, or for previous versions (from 2005 onwards), the workaround posted on this connect item shows a completely supported way of doing this without any need for size of data operations using ALTER TABLE...SWITCH . Also blogged about on MSDN here. Though the code to achieve this is not very simple and there are restrictions - such as the table being changed can't be the target of a foreign key constraint.
Example code.
Set up test table with no identity column.
CREATE TABLE dbo.tblFoo
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)
INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2
Alter it to have an identity column (more or less instant).
BEGIN TRY;
BEGIN TRANSACTION;
/*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
set the correct seed in the table definition instead*/
DECLARE @TableScript nvarchar(max)
SELECT @TableScript =
'
CREATE TABLE dbo.Destination(
bar INT IDENTITY(' +
CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1) PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)
ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
'
FROM dbo.tblFoo
WITH (TABLOCKX,HOLDLOCK)
EXEC(@TableScript)
DROP TABLE dbo.tblFoo;
EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
Test the result.
INSERT INTO dbo.tblFoo (filler,filler2)
OUTPUT inserted.*
VALUES ('foo','bar')
Gives
bar filler filler2
----------- --------- ---------
10001 foo bar
Clean up
DROP TABLE dbo.tblFoo
SQL Server: How to set auto-increment on a table with rows in it:
This strategy physically copies the rows around twice which can take a much longer time if the table you are copying is very large.
You could save out your data, drop and rebuild the table with the auto-increment and primary key, then load the data back in.
I'll walk you through with an example:
Step 1, create table foobar (without primary key or auto-increment):
CREATE TABLE foobar(
id int NOT NULL,
name nchar(100) NOT NULL,
)
Step 2, insert some rows
insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');
Step 3, copy out foobar data into a temp table:
select * into temp_foobar from foobar
Step 4, drop table foobar:
drop table foobar;
Step 5, recreate your table with the primary key and auto-increment properties:
CREATE TABLE foobar(
id int primary key IDENTITY(1, 1) NOT NULL,
name nchar(100) NOT NULL,
)
Step 6, insert your data from temp table back into foobar
SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;
Step 7, drop your temp table, and check to see if it worked:
drop table temp_foobar;
select * from foobar;
You should get this, and when you inspect the foobar table, the id column is auto-increment of 1 and id is a primary key:
1 one
2 two
3 three
如果您想通过设计人员进行此操作，则可以按照此处的说明进行操作，如果将现有列更改为可以为空时，则不允许保存更改
链接地址: http://www.djcxy.com/p/95652.html