Insert a Field (IDENTITY) into table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert a Field (IDENTITY) into table

Author

Topic

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-04-25 : 11:06:48

Hi,

I have a table with 1000000 records i try to add a field with following`spec.

[mkey] [int] IDENTITY(1,1) NOT NULL

I get the following meassage:-

yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

Then I Have the following error:-
Time out error

- How is the best way to copy a large table from one to anther.

regards

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 12:37:56

create another table with the identity column and do a


SET IDENTITY_INSERT <newtable> ON
INSERT INTO <NewTable> ( <columns>)
SELECT <columns> 
FROM <existingTable>
SET IDENTITY_INSERT <newtable> OFF

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 12:39:02

Ofcourse, you would need to add any Constraints/Indexes that you already have on the existing table onto the new table explicitly.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources