I was faced with a challenge: add a column to a table. It sounds simple enough, but the table already had data in it, and had an Identity column, so care had to be taken to preserve data as well as identities as they were used as part of a key on another table. After discussing possibilities with my DBA, we came up with the following approach.
1. Copy the main table to a backup table on the same database.
2. Drop the original table
3. Create the modified table structure including Identity declaration
4. Turn on Identity Insert so that columns usually protected and written only by the server can be written from the backup data, thus preserving the identities
5. Insert the backup data, sans new column, into the new table
6. Update the new table with default values for the new column (optional)
7. Turn off identity insert
8. Drop the backup table
You can run these steps one at a time to confirm they are working properly, or run them all at once provided you break the queries into separate work units with the GO statement, otherwise you’ll be attempting to write to columns that don’t exist at compile time.
Here’s a sample SQL script that accomplishes the above task.
SELECT * INTO BACKUP_MYTABLE from MYTABLE
DROP TABLE MYTABLE
CREATE TABLE MYTABLE
oldIdentityCol int NOT NULL IDENTITY(1,1),
Oldcolumn1 varchar(10), -- as appropriate to your original data structure
SET IDENTITY_INSERT MYTABLE ON
INSERT INTO MYTABLE (oldIdentityCol, oldColumn1,) SELECT oldIdentityCol, oldColumn1 from BACKUP_MYTABLE
SET IDENTITY_INSERT MYTABLE OFF
UPDATE MYTABLE SET NEWCOLUMN = 1 where NEWCOLUMN IS NULL – optionally populate your new colum
Drop TABLE BACKUP_MYTABLE