Actually you can't "natively" add the IDENTITY property to an existing column via T-SQL. You can in enterprise manager because it does a ton of stuff in the background to make it happen. The T-SQL work around is a pain, and I don't recommend trying it.
Daniel -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Brent Helms Sent: Wednesday, June 15, 2005 3:55 PM To: [email protected] Subject: More SQL (Updating a table with IDENTITY) For some reason, I cannot seem to locate a code example that properly adds the "IDENTITY (1,1) to a pre-existing column". This is what I have tried, and afterwards, i'll briefly tell you the reason. ALTER TABLE [dbo].[AccountInfo] ALTER COLUMN [AccountInfoID] [int] IDENTITY(1,1) NOT NULL I've tried replacing line 2 with a variety of things, but I have yet to be successful. If you get rid of everything after "int" it works. I'm trying to accomplish the following: 1) create a temp table mocking the table in question 2) populate the temp table with the values from the original 3) drop the original table 4) modify the script that re-creates the original table and then re-create it - constraints and IDENTITY will not be added back yet 5) push data to the original table from the temp table 6) add IDENTITY back to the ID columns, add constraints 7) drop the temp table Every step looks good, but I cannot get IDENTITY back into the original table. This hopefully is a syntax no-brainer? I know it is posisble because you can make the column an IDENTITY through Enterprise manager. Regards, Brent ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
