Monday, January 15, 2018

SQL-fu: Modify a Data Bearing Table

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
GO
DROP TABLE MYTABLE
GO
CREATE TABLE MYTABLE
(
                oldIdentityCol int NOT NULL IDENTITY(1,1),
Oldcolumn1 varchar(10),  -- as appropriate to your original data structure
Newcolumn int
)
Go
SET IDENTITY_INSERT MYTABLE ON
GO
INSERT INTO MYTABLE (oldIdentityCol, oldColumn1,) SELECT oldIdentityCol, oldColumn1 from BACKUP_MYTABLE
GO
SET IDENTITY_INSERT MYTABLE OFF
GO
UPDATE MYTABLE SET NEWCOLUMN = 1 where NEWCOLUMN IS NULL – optionally populate your new colum
GO
Drop TABLE BACKUP_MYTABLE
GO
 

No comments:

Post a Comment