June 23, 2009 at 5:37 am
How can we add identity property on existing table on primary key column.
The table is having data.
I am thinking to add a temp column move all data from primary key column to the temp column and then dropping all related fks depending on this pk.
1. Then dropping the pk
2. adding identity property
3. then moving data back from the temp column,
4. creating pk
Is it a good practice or I need to use temp table instead of temp column.
June 23, 2009 at 8:02 am
I think you need to clarify this a little.
Post the existing table def, and what you want the table def to be after the alteration.
If lots of foreign keys link to that table - its going to be messy
But whatever the eventual method take backups, test it out in a safe non-production environment until its working, and it may be advisable to do the production run in downtime or single user mode in case of locking/changes during your change.
June 23, 2009 at 8:08 am
you should be able to create a new column and make it identity and primary key:
Question: will this default your existing primary key column and uniqueness..
ALTER TABLE ABC
ADD NEWCOL int IDENTITY(1,1) PRIMARY KEY
Maninder
www.dbanation.com
June 23, 2009 at 1:00 pm
I will put table definition you will get clear picture..
I have a table : test with column A, B and C with datatype int
test(A int, B int, C int)........ here A is primary key.
We have some data in this table. Now I want to make A as identity.
June 24, 2009 at 1:28 am
OK That makes it a little easier than I first thought. I prefer the temp table solution.
I've run this test example, and it seems to work, you should be able to adapt this to your situation.
However, if your existing primary key is referenced elsewhere as a foreign key it will be more complicated - Let me know if this is the case.
USE [TempDB];
GO
CREATE TABLE [dbo].[Test](
[Code] int NOT NULL,
[Data] [varchar](100) NULL,
CONSTRAINT PK_Test
PRIMARY KEY CLUSTERED
(
[Code] ASC
)
) ON [PRIMARY];
GO
INSERT INTO dbo.Test
SELECT 1, 'AAAAAA' UNION ALL
SELECT 2, 'BBBBB' UNION ALL
SELECT 3, 'CCCCC';
BEGIN TRANSACTION
CREATE TABLE dbo.TempTest
(
CODE INT IDENTITY(1,1) PRIMARY KEY,
Data VARCHAR(100) NULL
) ON [PRIMARY];
GO
SET IDENTITY_INSERT dbo.TempTest ON;
-- get exclusive table lock on dbo.Test
INSERT INTO dbo.TempTest (Code, Data)
SELECT Code, Data FROM dbo.Test WITH (TABLOCKX);
GO
SET IDENTITY_INSERT dbo.TempTest OFF;
DROP TABLE dbo.Test;
EXECUTE sp_rename N'dbo.TempTest', N'Test', 'OBJECT' ;
ROLLBACK
-- COMMIT
DROP TABLE dbo.Test;
July 14, 2009 at 1:31 am
How to add identity Property to an existing table In sql server 2005
July 15, 2009 at 10:51 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply