SQL Idempotence

Question

I have a post deployment script in my SQL database project, which registers default values that should be part of the database. I'd want this script to be able to be run multiple times without changing the outcome, so I can run it manually if I need to add just a little modification.

INSERT INTO [UserRole] ([Name])
SELECT 'Regular'
UNION ALL
SELECT 'Admin'

INSERT INTO [UserRight] ([Name])
SELECT 'CanAccessApplicationLogs'
UNION ALL
SELECT 'CanAccessApplicationJobs'

INSERT INTO [UserRoleRight] ([UserRoleId], [UserRightId])
SELECT  [Ro].[Id], [Ri].[Id] FROM [UserRight] Ri, [UserRole] Ro
 WHERE ([Ro].[Name] = 'Admin' AND
        [Ri].[Name] = 'CanAccessApplicationLogs')
    OR ([Ro].[Name] = 'Admin' AND
        [Ri].[Name] = 'CanAccessApplicationJobs')

I doubt performance will ever be a problem since this script will just be inserting roles and rights. So what would be the cleanliest way to check if each row exists, and only insert those that aren't already in the tables?

Voltaic

Answer 1

Wrapping each insert in an IF statement would work. Not sure if it's the cleanest way though.

So your first INSERT would become:

IF NOT EXISTS(SELECT * FROM UserRole WHERE Name = 'Regular')
BEGIN
  INSERT INTO [UserRole] ([Name])
  SELECT 'Regular'
  UNION ALL
  SELECT 'Admin'
END

Rhizobium

Answer 2

A MERGE can be a lot cleaner and will check each row separately without needing IF statements around every one.

http://coding.abel.nu/2012/08/idempotent-db-update-scripts/ has a good example

Rashidarashidi

This is cool! It seems it even accommodates entries you've removed. My MySQL is a bit rusty, so I need to figure out how to generalize this. – Arvind

Wait, nope, this is not MySQL. ON DUPLICATE KEY I think is the way to go. No accommodating removed entries I suppose. – Arvind