Instead of alter table column to turn IDENTITY on and off, turn IDENTITY_INSERT on and off
- by Kevin Shyr
First of all, I don't know which version of SQL this post (http://www.techonthenet.com/sql/tables/alter_table.php) is based on, but at least for Microsoft SQL Server 2008, the syntax is not:
ALTER TABLE [table_name]
MODIFY [column_name] [data_type] NOT NULL;
Instead, it should be:
ALTER TABLE [table_name]
ALTER COLUMN [column_name] [data_type] NOT NULL;
Then, as several posts point out, you can't use T-SQL to run an existing column into an IDENTITY column. Instead, use the IDENTITY_INSERT to copy data from other tables. http://msdn.microsoft.com/en-us/library/ms188059.aspx
SET IDENTITY_INSERT [table_name] ON
INSERT ....
SET IDENTITY_INSERT [table_name] OFF
http://www.sqlservercentral.com/Forums/Topic126147-8-1.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65257
