What are the challenges you face when working across database platforms? Take the survey
Options

Lookup data with environment-specific ID values

LogixologyLogixology Posts: 2
edited August 31, 2016 12:16AM in ReadyRoll
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.

Comments

  • Options
    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 have any questions or clarifications, please let me know.
    Daniel Nolan
    Product Manager
    Redgate Software
Sign In or Register to comment.