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