I'm altering a table in my dev environment, mostly because I didn't capitalize one column and we're picky about these things. So what I need to do (in order to keep existing dev data) is to copy the data to another table, drop and recreate the original table, and reinsert the data.
CREATE TABLE [dbo].[FreshPrince_TEMP] ( [FreshPrinceID] int PRIMARY KEY CLUSTERED , [prince] int NULL, [Carlton] int NULL ) ON [PRIMARY] INSERT INTO FreshPrince_TEMP SELECT FreshPrinceID, Prince, Carlton FROM FreshPrince DROP TABLE FreshPrince CREATE TABLE [dbo].[FreshPrince] ( [FreshPrinceID] int IDENTITY(1,1) PRIMARY KEY CLUSTERED , [Prince] int NULL, [Carlton] int NULL ) ON [PRIMARY]Of course my table has an IDENTITY column (we also like simple surrogate keys). What happens when you try to insert data to FreshPrince?
Msg 8101, Level 16, State 1, Line 1 An explicit value for the identity column in table 'FreshPrince' can only be specified when a column list is used and IDENTITY_INSERT is ON.So we have a problem: you can't insert values into an IDENTITY column because it auto generates its own values. Enter: IDENTITY_INSERT!
IDENTITY_INSERT just lets you disable the IDENTITY property so you can do what you need to do. Use with caution, of course, as you're messing with the fundamental purpose of the IDENTITY property. Oh, and remember to use your column list, or it won't work! Let's watch it in action:SET IDENTITY_INSERT FreshPrince ON INSERT INTO FreshPrince (FreshPrinceID, Prince, Carlton) — column list is required! SELECT FreshPrinceID, Prince, Carlton FROM FreshPrince_TEMP
Beautifully done. IDENTITY_INSERT has session scope, so only your session will be able to insert to the identity row explicitly. AND only one table in a session can have IDENTITY_INSERT ON at a time. Remember to SET IDENTITY_INSERT OFF, though, to be thorough. If you leave it ON you might forget in the same session, and mess yourself up. How's that for technical jargon?
-Jen McCown, http://www.MidnightDBA.com