I struggled with this and I am sure others have done so. I am always asking questions but have little to contribute. So this is my first contribution. I had a table with an int for the ID column but it did not auto-increment. I tried altering the table to no avail - it can't be done at least easily. I came up with the following script which worked. You must name all the columns in the insert statement and not use * or you will get an error. Also, keep in mind to rename any constraints different than the old table because they are still in there until the table is dropped. I used a rename here just in case, but you can drop the old table just as well. If anyone has any further comments or pitfalls, go ahead and put them here. I want to learn, too. (I was going to post this as a question, but I found the answer so I thought I would post my whole struggle for others to benefit from.)IF OBJECT_ID('dbo.v2_Office','U') IS NOT NULL DROP TABLE dbo.v2_OfficeGOCREATE TABLE v2_Office ( [OfficeID] INT IDENTITY(1,1) NOT NULL, [OfficeName] NVARCHAR(15) NULL, [Office] NVARCHAR(4) NULL, [Notes] NVARCHAR(50) NULL,CONSTRAINT [aaaaaaOffice_PK] PRIMARY KEY NONCLUSTERED ( OfficeID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]SET IDENTITY_INSERT v2_Office ONINSERT INTO v2_Office (OfficeID,OfficeName,Office,Notes)SELECT OfficeID,OfficeName,Office,Notes FROM OfficeSET IDENTITY_INSERT v2_Office OFF/*DROP or RENAME TABLE Office*/EXEC sp_rename 'Office', 'OfficeOld'EXEC sp_rename 'v2_Office', 'Office'Duane
Set Identity auto-increment on column
Posted - 2009-06-05 : 12:42:19
