I have the table in SQL Server 2008. It has empId, which is the int of type. It is not identity for now. I want to write the query (so that I can maintain the record of changes in DB) to make this columns as identity column. This table have no data currently.
+3
A:
It can be done but not easily. According to this article if you do this through SSMS and then generate a script that basically:
- Create similar table structure as example1, say Tmp_Example1 with Identity Column.
- Set IDENTITY_INSERT ON on new table just created.Step 3: Insert all values from example1 to Tmp_Example1
- Set IDENTITY_INSERT OFF on Tmp_Example..
- Drop example1 (What is going on… dropping our original table???)
- Rename Tmp_Example1 to Example1. Commit Transaction
If this table doesn't have any data in it as you say, then you are probably better off just doing this through SSMS (steps are provided in the article i link to). Is there a reason you want to do this using SQL?
UPDATE
I was reading an article that another user posted up earlier and found this which seems much easier than my above method:
ALTER TABLE SOURCE_TB ADD ID_II INT IDENTITY(1,1)
ALTER TABLE SOURCE_TB DROP COLUMN ID
EXEC sp_rename 'SOURCE_TB.ID_II' ,'ID','COLUMN'
But again, it probably makes more sense to do this through SSMS. Good to know though...
Abe Miessler
2010-09-21 05:38:54
+1 good one ...............
Pranay Rana
2010-09-21 05:53:19
@Pranay, I was reading the article you posted earlier and they had the code that I posted in my update which appears to work. I think you had something different from this in your solution though?
Abe Miessler
2010-09-21 05:55:15
I agree with this, but some how I want to update existing column. I know it will no impact to follow this , but. want to pure alter table , and want to set identity . this is wrong --Drop and then recreate. sql server must have such facility to change the column property as identity.
Lalit
2010-09-21 06:02:27
@Lalit: **NO** - SQL Server has **no** such function. If you have an INT column, you **cannot** modify / alter it into being an IDENTITY column. Abe's solution (creating a new IDENTITY column) is the only viable solution to this problem.
marc_s
2010-09-21 07:01:14
ok thanks. finally I did drop and create total table.
Lalit
2010-09-21 13:41:47
+1
A:
You have no data, so script the table as drop and recreate. The change the field to Employeeid int identity(1,1) not null. Then recreate the table. No fuss no muss.
HLGEM
2010-09-21 18:41:07