alter column to an identity column
-
How can I alter a table column into an Identity column using sql transact??
I Tried this way but SQL Server returns an error.
alter table orders
alter column id_order int not null Identity(1,1)
10x.
Heber Zachi
-
You cannot have nulls in the column. If you are receiving an error there are probably nulls.
Scripting from enterprise manager changing a column to Identity, it creates a table called Tmp_youtablename with identity
Then it moves the data to tmp_yourtable from your table
then it drops yourtablename
then it renames to yourtablename from tmp_yourtablename.
Use the same way it works every time.
Code:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Orders
(
id_Order int NOT NULL IDENTITY (1, 1),
stuffy char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Orders ON
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (id_Order, stuffy)
SELECT id_Order, stuffy FROM dbo.Orders TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Orders OFF
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
COMMIT
-
not sure if it possible to change a column into identity column after it has been created. BOL does not state this is explicitly though; all references within BOL to adding an identity column applied to new columns only.
/* --- cut here --- */
use tempdb
go
begin tran
create table orders(
id_order int
)
go
alter table orders alter column id_order int identity(1,1)
/* error here:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'identity'.
*/
go
alter table orders add id_order2 int identity(1,1)
/* can only create identity for new columns */
go
select * from orders
rollback;
/* --- cut here --- */
Billy
-
Run this:
alter table orders
drop column id_order
alter table orders
alter column id_order int not null Identity(1,1)
-
Sorry, too fast reply:
alter table orders
drop column id_order
alter table orders
add id_order int not null Identity(1,1)
-
Markus Veretin (7/14/2004)
Sorry, too fast reply:
alter table orders
drop columnid_order
alter table orders
addid_orderint not null Identity(1,1)
If you just drop the existing column and add a new column with the same name but define it as identity, you’ll might modify the value of the primary key for some of the records in the table. There is no guarantee that the new column will get the same values as the old columns.
Since there is no way to modify the column itself to be an identity column, you’ll have to create a new table with an identity column, insert the data from the old table into the new table (with set identity_insert on you’ll get the same values in the new column as you had in the old column), rename or drop the original table (It is better to rename it first, so you’ll be able to roll back the modification if something goes wrong), rename the new table to the original table’s name, recreate the foreign keys that referenced the original table and run dbcc checkident to initialize the identity’s seed to the next number.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
alter column to identity is very easy in ms sql server 2005
just right click on column you want to alter and click on design and change identity to YES
Enjoy!!!!!
-
pawantiwari.3987 (3/18/2011)
alter column to identity is very easy in ms sql server 2005just right click on column you want to alter and click on design and change identity to YES
Enjoy!!!!!
Okay now try to get that into a script to be run on a production server.
FYI this can also be done in SQL2000 Enterprise Manager. But as that and SSMS doesnt do anything magically. They are still restricted to what SQL can do and what can be scripted. Now the script that gets created in both 2000 and 2005 is that a new table is created and all data is copied to it. The old table gets deleted and the new one gets renamed.
There is more to SQL then point and click (but can be useful for getting a script easy :-D)
This is part off the script that gets created (on a little test table i created just for the occation)
CREATE TABLE dbo.Tmp_tbl(
i int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_tbl ON
GO
IF EXISTS(SELECT * FROM dbo.tbl)
EXEC('INSERT INTO dbo.Tmp_tbl (i)
SELECT i FROM dbo.tbl TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_tbl OFF
GO
DROP TABLE dbo.tbl
GO
EXECUTE sp_rename N'dbo.Tmp_tbl', N'tbl', 'OBJECT'
GO
Oh and this would also indicate that you cant add the identity property to an existing column.
/T
-
or just do this
procedure [dbo].[MakeIndendity](@identityField varchar(50), @fieldsDeclare varchar(255),@fieldsSelect varchar(255),@tableName varchar(50))
as
Begin
EXEC('CREATE TABLE Tmp_tbl(' + @fieldsDeclare + ')')
SET IDENTITY_INSERT Tmp_tbl ON
EXEC('INSERT INTO Tmp_tbl (' + @fieldsSelect + ') SELECT ' + @fieldsSelect + ' FROM ' + @tableName + ' TABLOCKX')
SET IDENTITY_INSERT Tmp_tbl OFF
EXEC('DROP TABLE ' + @tableName)
EXECUTE sp_rename N'Tmp_tbl', N@tableName, 'OBJECT'
EXEC('ALTER TABLE [dbo].' + @tableName + ' WITH NOCHECK ADD CONSTRAINT [PK_' + @tableName + '] PRIMARY KEY CLUSTERED (' + @identityField + ') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY];')
End
be better to just pass the table and the column and figure loop the fields itself but i was too lazy i guess
Michael Evanchik
Microsoft MVP
-
just to fix a few bugs
ALTER procedure [dbo].[MakeIndendity](@identityField varchar(50), @fieldsDeclare text,@fieldsSelect text,@tableName varchar(50))
as
Begin
EXEC('CREATE TABLE Tmp_tbl(' + @fieldsDeclare + ')')
SET IDENTITY_INSERT Tmp_tbl ON
EXEC('INSERT INTO Tmp_tbl (' + @fieldsSelect + ') SELECT ' + @fieldsSelect + ' FROM ' + @tableName + ' TABLOCKX')
SET IDENTITY_INSERT Tmp_tbl OFF
EXEC('DROP TABLE ' + @tableName)
EXEC('BEGIN TRY while (select 1 from ' + @tableName + ') = 1 begin if exists(select * from ' + @tableName + ') break else continue end END TRY BEGIN CATCH END CATCH ')
EXECUTE sp_rename N'Tmp_tbl', @tableName, 'OBJECT'
EXEC('ALTER TABLE ' + @tableName + ' WITH NOCHECK ADD CONSTRAINT [PK_' + @tableName + '] PRIMARY KEY CLUSTERED (' + @identityField + ') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]')
End
Michael Evanchik
Microsoft MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply