1

How can a table definition be manually refreshed without also manually crafting a bunch of ALTER TABLE statements, or dropping and recreating the table?

This is normally handled by the publish process (e.g. SSDT), but during testing of schema alterations and their migration scripts, I need to revert the table definitions, and the managed rebuild/publish process through Visual Studio (and getting specific versions from TFS) takes quite a long time.

For example, if I have this new version of the table after updating the schema

CREATE TABLE [dbo].[MyAssociativeTable]
(
    [SurrogateID]   INT NOT NULL IDENTITY(1, 1)
    ,[ForeignID1]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable1]([ID])
    ,[Flag1]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag1] DEFAULT(0)
    ,[ForeignID2]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable2]([ID])
    ,[Flag2]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag2] DEFAULT(0)
    ,[DataColumn1]  REAL NULL CONSTRAINT [CK_MyAssociativeTable_Data1] CHECK(ISNULL([DataColumn1], 0) >= 0)
    ,[ExtraColumn1] VARCHAR(MAX) NULL
    ,CONSTRAINT [PK_MyAssociativeTable] PRIMARY KEY
    (
        [ForeignKey1]
        ,[ForeignKey2]
    )
    ,CONSTRAINT [CK_MyAssociativeTable_Case1] CHECK
    (
        [Flag1] = 0
        OR [Flag2] = 0
    )
)
GO
CREATE UNIQUE INDEX [AK_MyAssociativeTable_F1]
ON [dbo].[MyAssociativeTable]([ForeignID1])
WHERE [Flag1] = 1

and I want to revert it to the original version (removing ExtraColumn1 is ok) so I can insert invalid rows to test migration scripting on

CREATE TABLE [dbo].[MyAssociativeTable]
(
    [SurrogateID]   INT IDENTITY(1, 1)
    ,[ForeignID1]   INT
    ,[ForeignID2]   INT
    ,[Flag1]        BIT
    ,[Flag2]        BIT
    ,[DataColumn1]  REAL
    ,PRIMARY KEY([SurrogateID])
)

what are my options?

Note that columns are also being re-ordered. Handling this would be nice, but is unimportant.

  • I would use a Schema Compare for this. Are you familiar with it? – Jacob H Sep 26 at 16:24
  • No, we have never used that tool before, but should? – Elaskanator Sep 26 at 17:45
0

One approach that works (only if there are no foreign keys referring to the table) is to create a backup of the original table with its original data.

Before starting to test the migration:

  1. Grab a copy of the old table definition to define a copy of it, with a unique name (e.g. append "_bak" to the table name) and all named constraints (using sp_rename on just the table name will not automatically update named constrains).
  2. SET IDENTITY_INSERT [bak] ON
  3. Write a simple INSERT INTO [bak] (\every column\) SELECT \every column\ FROM [original] (the column list must be specified, otherwise you will get error 8101 for not explicitly writing out the identity column (why?))
  4. SET IDENTITY_INSERT [bak] OFF

Note 1: Make sure to not enable the advanced SSDT publish option "drop objects in target not in source" or this table will get dropped if you publish.

Note 2: Computed columns must not be included in the \every column\ list.

Then each time you want to revert the table definition and populate it with the preexisting records:

  1. DROP TABLE [target]
  2. Copy the original table definition and run that CREATE TABLE ... code
  3. SET IDENTITY_INSERT [target] ON
  4. INSERT INTO [target](\every column\) SELECT \every column\ FROM [bak]
  5. SET IDENTITY_INSERT [target] OFF

This is still far too manual of a process however (and breaks on foreign keys).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.