Alex's Slip-box

These are my org-mode notes in sort of Zettelkasten style

mssql data migration with rollback

# Example data migration with rollback (MSSQL)

# Migration

 1: IF NOT EXISTS (SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'rollback_schema')
 2: BEGIN
 3:   EXEC sp_executesql N'CREATE SCHEMA rollback_schema'
 4: END
 5: 
 6: CREATE TABLE rollback_schema.update_foos_bar (id INT, bar VARCHAR(255);
 7: 
 8: IF EXISTS (select 1 from foos where bizz = 'Update this')
 9: BEGIN
10:   UPDATE foos
11:   SET bar = 'Updated value'
12:   OUTPUT deleted.id, deleted.bar INTO rollback_schema.update_foos_bar
13:   WHERE bizz = 'Update this'
14: END

# Rollback

1: UPDATE f
2: SET bar = r.bar
3: FROM foos f
4: JOIN rollback_schema.update_foos_bar
5: ON f.id = r.id
6: 
7: DROP TABLE rollback_schema.update_foos_bar