Lookup data with environment-specific ID values

Logixology

A colleague recommended ReadyRoll and believes it can do what I am looking for.

Here is our challenge: In a database where we have no control over structurally there are some lookup tables. The issue is that the ID in one lookup table in one environment (DEV) doesn't match the same lookupID in Production. I need some tool that will allow for this disparity when I go between environments without having to manually change hard coded ID's.

For example (using RedGate for the example). I have a sales table and SoftwareID = 7 for SQLCompare.
In my SQL Query I am doing a

SELECT * FROM Software WHERE SoftwareID = 7

. Well in Production SoftwareID for SQLCompare is 15.
How can we manage the different lookup values by environment.

dnlnln

Thanks for your interest in ReadyRoll! For environment-specific data deployment, we recommend following an offline approach to managing your static data. This is because the online method (which ReadyRoll uses when you import data from your connected database) cannot take into account the differences between data in each of your environments during the comparison process. You can read more about these two different approaches in the documentation:
https://documentation.red-gate.com/display/RR1/Static+Data

Following the offline approach to deploying environment-specific data involves defining your static data in a re-usable script file. Within the script file, you can use variables to drive which ID is used in each environment. For example, define a project variable called $(Environment) and give it a Default value of Development. In your MERGE statement (which will declaratively handle the insert/update/delete of data in table), you can use the $(Environment) variable in a CASE statement to ensure the correct ID is used:

SET NOCOUNT ON

SET IDENTITY_INSERT [Software] ON

MERGE INTO [Software] AS Target
USING (VALUES
  (CASE '$(Environment)' WHEN 'Production' THEN 15 ELSE 7 END, 'Software A')
 ,(CASE '$(Environment)' WHEN 'Production' THEN 16 ELSE 8 END, 'Software B')
) AS Source ([SoftwareId],[SoftwareName])
ON (Target.[SoftwareId] = Source.[SoftwareId])
WHEN MATCHED AND (
	NULLIF(Source.[SoftwareName], Target.[SoftwareName]) IS NOT NULL OR NULLIF(Target.[SoftwareName], Source.[SoftwareName]) IS NOT NULL) THEN
 UPDATE SET
  [SoftwareName] = Source.[SoftwareName]
WHEN NOT MATCHED BY TARGET THEN
 INSERT([SoftwareId],[SoftwareName])
 VALUES(Source.[SoftwareId],Source.[SoftwareName])
WHEN NOT MATCHED BY SOURCE THEN 
 DELETE
;
GO
PRINT 'Software rows affected by MERGE: ' + CAST(@@ROWCOUNT AS VARCHAR(100));
GO

I generated the above MERGE statement using a free tool called sp_generate_merge, and then added the CASE statements (as mentioned in the article on "static data", above). I've created a sample ReadyRoll project that contains the above code and variable configuration: http://download.ready-roll.com/EnvSpecificLookupIDs_Sample_RRproject.zip

Add this code as a Post-Deployment script within your ReadyRoll project and deploy the solution to apply the changes to your database. To simulate a Production deployment, open the project properties, switch to the SQLCMD variables tab and change the Default value for the variable to Production, and deploy the solution again. You'll notice that 4 rows are changed (2 rows deleted, 2 rows inserted) although in a "real" Production development, only the rows that have changed will actually be affected (if any).

In terms of deploying your project to different environments:

• If you're using the PowerShell command line, you can specify a different a value for the "Environment" variable before executing the deployment script. More on this method here: https://documentation.red-gate.com/display/RR1/PowerShell+Deployment
• If you're using Octopus Deploy to deploy your ReadyRoll database package, any variables you define in Octopus will be automatically available to your ReadyRoll project. So you just need to add a variable to Octopus called Environment with the appropriate environment-scope and value. More information about this method here: https://documentation.red-gate.com/display/RR1/Octopus+Deploy

If you have any questions or clarifications, please let me know.