Adding new column to static data table

minijebu

Hi,

I have a table which contains static data. Its linked to the source control.
I am trying to add a new column to it.
Its NOT NULL-able.But without any default value constraint.
Is this possible?

Kind Regards
minijebu

AlexYates

Good morning,

This is a tricky unusual case that isn't handled well out of the box. The problem is ordering.

1. SQL Compare tries to add the new column, but fails because of the NOT NULL constraint.
2. Data Compare squirts in the data, but it never gets to this point because step 1 has already failed.

Possible work arounds:

UNLINK/RE-LINK STATIC DATA
1. Unlink static data
2. Deploy new column as NULLABLE all the way up to prod
3. INSERT static data manually all the way to prod, or relink static data to source control and then deploy all the way to prod
3. Add NOT NULL constraint, commit to source control and deploy all the way to PROD
4. Re-link static data (if not already done so)

This option is probably the simplest, but it requires a multi-step deployment. If you have short release cycles and a good DevOps maturity this should not be too big an issue, but if you have long release cycles and poor release management processes this can be a pain to manage.

PRE/POST DEPLOYMENT SCRIPTS
SQL Source Control has recently released a new feature which, at the time of writing, is still only available via the frequent updates release channel:
https://documentation.red-gate.com/soc6/common-tasks/working-with-pre-post-deployment-scripts
1. Unlink static data and add a filter to ignore the table schema
2. Add a pre-deploy script to add the column as NULL, insert appropriate data and then apply the NOT NULL constraint. Your script needs to be rerunnable, so consider adding an IF EXISTS statement at the top and using a MERGE statement to squirt in the data.
3. Deploy the change all the way to production.
4. Remove the table filter and re-link the static data. Also, remove the pre-deploy script. Once the update has been deployed everywhere you don't need it any more.

This option requires less manual intervention/project management but it does rely on more complex and bleeding edge features that have not yet been released on the regular SQL Source Control release channel.

minijebu

Hi Alex,
I tried the first method. Deployment went ok.
But now,the issue is,when we do a  PULL and try to APPLY the changes on other developers' local instances,it fails.

On the 'Get Latest' tab, we checked the differences in the versions on local instance and source control.
The latest version on the source control included

• The structural change of the table(TableA),ie the new column(newColumnLevel) NOT NULLable is there.

• The static data changes,ie,The new values are there in the new column

But once ,Apply changes is clicked,it failed
The script is actually trying to create the table with the new column NOT NULLable and then it uses an INSERT statement which do not consider the new column at all.

Something like this :

CREATE TABLE [dbo].[RG_Recovery_1_TableA]<div>(</div><div>[id] [int] NOT NULL,</div><div>[description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,</div><div>[cDate] [datetime] NOT NULL CONSTRAINT [DF_TableA_cDate] DEFAULT (getdate()),</div><div>[<b>newColumnLevel] [tinyint] NOT NULL</b><br></div><div>) ON [PRIMARY]</div><div>GO<br></div><div>IF @@ERROR <> 0 SET NOEXEC ON</div>GO<br>

INSERT INTO [dbo].[RG_Recovery_1_TableA]([id], [description], [cDate]) SELECT [id], [description], [cDate] FROM [dbo].[TableA]--!!!!<i>the new column is not taken here and throws error</i>.<br><div>GO<br><div>IF @@ERROR <> 0 SET NOEXEC ON</div>GO</div>

DROP TABLE [dbo].[TableA]GO<br><div>IF @@ERROR <> 0 SET NOEXEC ON</div>GO<br>

EXEC sp_rename N'[dbo].[RG_Recovery_1_TableA]', N'TableA', N'OBJECT<br>GO&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>&nbsp;IF @@ERROR <> 0 SET NOEXEC ON<div>GO<br></div>

Kind Regards
minijebu

AlexYates

Fair point, i suppose my instructions for option 1 should have read:

UNLINK/RE-LINK STATIC DATA
1. Unlink static data
2. Deploy new column as NULLABLE all the way up to prod ****AND TO EVERY DEV WORKSTATION****
3. INSERT static data manually all the way to prod ****AND TO EVERY DEV WORKSTATION****, or relink static data to source control and then deploy all the way to prod ****AND TO EVERY DEV WORKSTATION****
3. Add NOT NULL constraint, commit to source control and deploy all the way to PROD ****AND TO EVERY DEV WORKSTATION****
4. Re-link static data (if not already done so) 

In retrospect, perhaps the following is a better solution all round:

PRE/POST DEPLOYMENT SCRIPTS (v2)
1. Unlink static data table
2. Add a pre deploy to:
  a. Check if target table is in the before state and that it already holds data
  b. If so, create a new table called OriginalTableName_Temp
  c. Copy all data to new temp table
  d. Truncate original table
3. Add a post-deploy to:
  a. check if OriginalTableName_Temp exists
  b. if so, copy all data, including new default data for new NOT NULL col into original table (by the time this script runs, the new col should exist).
  c. delete OriginalTableName_Temp
4. Commit your new pre- and post-deploy script, along with your new NOT NULL column as a single commit.
5. Deploy this change to all environments, including prod and all dev workstations
6. Re-link static data

*

For now, you probably need to manually patch all the other dev workstations. Sorry I forgot to include dev workstations in my original answer. Forgive me, I'm a only fallible human. :-)

minijebu

Hi Alex,

I have not thought about the option of moving the change to every workstation.
I am trying to see if on each DEV work stations,we can do a PULL once and APPLY all the changes at once.

Since we have so many DEV workstations , i find it difficult to apply the changes on each of them once i PUSHed each step in this process.I hope i understood you correctly on : 'AND TO EVERY DEV WORKSTATION' .

And since we are in the moving phase of of automated deployment,currently the production is not yet part of it.We are working on it.
Right now,the deployment is to a shared DEV environment only.

So,i can actually rollback the changes i did and I will go for PRE/POST DEPLOYMENT SCRIPTS.

Kind Regards
minijebu

AlexYates

Sounds like a sensible decision.

Using pre/post should mean the "deploy to all workstations" step is just a simple "get latest"/"apply changes".

minijebu

Please correct me if i am not getting it right.

In the 5th and 6th steps of using PRE/POST DEPLOYMENT SCRIPTS,
5. . Deploy this change to all environments, including prod and all dev workstations
6.   Re-link static data

This means ,that i can re-link the table back to source control, only after making sure that, all DEV workstations(which is local to each developer) have done a PULL and applied the changes.

So,if any DEV workstation fail to do so,and trying to do a PULL and APPLY CHANGES after i re-link the table ,then, it can fail.

Kind Regards
minijebu