Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

alter column to an identity column Expand / Collapse
Author
Message
Posted Tuesday, July 13, 2004 1:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 21, 2004 9:14 AM
Points: 32, Visits: 1

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




Post #126147
Posted Tuesday, July 13, 2004 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 30, 2006 8:47 AM
Points: 123, Visits: 1
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
Post #126149
Posted Tuesday, July 13, 2004 9:54 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 8:19 PM
Points: 514, Visits: 6

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




Post #126193
Posted Wednesday, July 14, 2004 12:40 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 740, Visits: 1,531

Run this:

 

alter table orders
drop column id_order 

alter table orders
alter column id_order int not null Identity(1,1)

 




Post #126242
Posted Wednesday, July 14, 2004 12:42 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 740, Visits: 1,531

Sorry, too fast reply:

alter table orders
drop column id_order 

alter table orders
add  id_order int not null Identity(1,1)




Post #126243
Posted Thursday, April 30, 2009 1:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #707459
Posted Friday, March 18, 2011 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 4:49 AM
Points: 8, Visits: 225
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!!!!!
Post #1080309
Posted Friday, March 18, 2011 7:02 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
pawantiwari.3987 (3/18/2011)
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!!!!!


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 )

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
Post #1080339
Posted Wednesday, April 27, 2011 12:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 24, 2013 1:20 PM
Points: 70, Visits: 86
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
www.before0day.com
www.MikeEvanchik.com
Post #1099733
Posted Friday, April 29, 2011 7:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 24, 2013 1:20 PM
Points: 70, Visits: 86
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
www.before0day.com
www.MikeEvanchik.com
Post #1100879
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse