Instead of alter table column to turn IDENTITY on and off, turn IDENTITY_INSERT on and off

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

Print | posted on Wednesday, March 2, 2011 11:24 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski