SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


alter column to an identity column


alter column to an identity column

Author
Message
heber
heber
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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





Edward McGovern
Edward McGovern
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 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
bp
bp
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 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





Markus Veretin
Markus Veretin
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 1869

Run this:

alter table orders
drop column id_order

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





Markus Veretin
Markus Veretin
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 1869

Sorry, too fast reply:

alter table orders
drop column id_order

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





Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3645 Visits: 6503
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/
$QLdb@
$QLdb@
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 228
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!!!!!
tommyh
tommyh
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1774 Visits: 2000
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 :-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
xgcmcbain
xgcmcbain
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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
xgcmcbain
xgcmcbain
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search