Adding IDENTITY to a primary key column of existing table
-
When you make the change in EM, save the sql change script so you can see what EM is doing. Chances are it is creating a new table, copying data across, deleting the old table then renaming the new one!
-
Nope.
As Guy said, EM will actually create a new table with the IDENTITY setting, copy all your data into the new table, delete the old one, and rename the new table to the old name.
Julian Kuiters
juliankuiters.id.au -
Yea, I am aware that EM create a new table with the IDENTITY setting, copy all your data into the new table, delete the old one, and rename the new table to the old name.
But my question is , is there any possbility by TSQL code to alter the
table with "Identity" property.
Like " Alter Table tablename....."
-
" But my question is , is there any possbility by TSQL code to alter the
table with "Identity" property."
You can only add a new column with the identity property to the table.
You cannot change the identity property of an existing column with T-SQL syntax.
The following procedure could be applied:
1. Create New table with same structure as old but with Identity Property set. ( With all fk constraints etc... )
2. Set Identity_Insert On for the newly created table
3. Copy the data from original table to new table
4. Set Identity_Insert Off for the newly created table
5. Drop the original table
6. Rename the New table with Identity Column to the original name
( Of course you could rename the original table first and create the new table (with identity property) with that name )
[EDIT: oops guess this is more or less what EM does
]/rockmoose
You must unlearn what You have learnt -
Since "SET IDENTITY_UPDATE <table> ON" statement does not exist, you can't do ALTER TABLE <table> ALTER COLUMN <pk-col> IDENTITY. So as rockmoose says you can only add the identity property to a new column. If your pk column does not have gaps in the values you could add a column with the identity property, drop the pk constraint, drop the pk column, rename the new pk column to the old pk column, and recreate the pk. But then your pk column will now be at the end of the table.
Jeff
-
?
Unless I'm mistaken, all you have to do in EM is go to Design Table, click on the column you want to make an Identity; then on the consumer tab, there should be a piklist for identity (5th down; it's greyed out when the column selected is not a key), and choose between No, Yes, and Yes (not for replication)
This is very much an Access functionality
-
This procedure creates a NewID column copies everything from ID to NewID, drops the ID column and renames NewID to ID. Now While creating the NewID column you can set it as an identity column.
Basically it does whatever EM does under the hood.
Hope this helps
create procedure sproc_alterColumnProperty
(
@tablename varchar(100)
)
as
DECLARE @ALTERTABLESTM NVARCHAR(4000)
DECLARE @UPDATESTM NVARCHAR(4000)
DECLARE @ALTERTABLESTM2 NVARCHAR(4000)
DECLARE @ALTERTABLESTM3 NVARCHAR(4000)
DECLARE @RENAMESTM VARCHAR(500)
DECLARE @tableobjNID varchar (100)
DECLARE @tableobjID varchar (100)
SET @tableobjNID = ''''+@tablename+'.new_ID'''
SET @tableobjID = ''''+@tablename+'.id'''
Set @ALTERTABLESTM = N'ALTER TABLE '+ @tablename + N' ADD new_ID int null'
EXEC SP_EXECUTESQL @ALTERTABLESTM
Set @UPDATESTM = N'update '+ @tablename + N' set new_ID = id'
EXEC SP_EXECUTESQL @UPDATESTM
Set @ALTERTABLESTM2 = N'alter table '+ @tablename + N' alter column new_ID int not null'
EXEC SP_EXECUTESQL @ALTERTABLESTM2
Set @ALTERTABLESTM3 = N'alter table '+ @tablename + N' drop column id'
EXEC SP_EXECUTESQL @ALTERTABLESTM3
Set @RENAMESTM = @tableobjNID
exec sp_rename 'MYTABLE.new_ID', 'ID', 'COLUMN'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply