Created
March 13, 2019 13:21
-
-
Save lontivero/9654459be80e49e65a3fbb9621bfaa78 to your computer and use it in GitHub Desktop.
SQL Migration Script Template
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE [DatabaseVersion] | |
( | |
Version [varchar](50), | |
UpdatedBy [varchar](50), | |
UpdatedOn [datetime], | |
Reason [varchar](1000) | |
); | |
INSERT INTO [DatabaseVersion] (Version, UpdatedBy, UpdatedOn, Reason) | |
VALUES ('1.0.0', 'Victor Hugo', getDate(), 'Database baseline'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ########################################################################################################################## | |
-- This file contains a script template used to create database migration scripts for each new version of Argos. | |
-- ########################################################################################################################## | |
-- ########################################################################################################################## | |
DECLARE @currentVersion [nvarchar](50) | |
DECLARE @expectedVersion [nvarchar](50) | |
DECLARE @newVersion [nvarchar](50) | |
DECLARE @author [nvarchar](50) | |
DECLARE @comment [nvarchar](1000) | |
-- ################################################ BEGIN OF UPDATE VERSION DATA ############################################# | |
SET @expectedVersion = '1.0.0' | |
SET @newVersion = '1.1.0' | |
SET @author = 'Victor Hugo' | |
SET @comment = 'Lista de indicadores' | |
-- ################################################ END OF UPDATE VERSION DATA ############################################### | |
SELECT @currentVersion = (SELECT TOP 1 [Version] FROM [DatabaseVersion] ORDER BY Id DESC) | |
IF @currentVersion = @expectedVersion | |
BEGIN TRY | |
BEGIN TRAN | |
-- ################################################ BEGIN OF SCRIPT ########################################################## | |
-- | |
-- Add database modifications here. | |
-- | |
--################################################# END OF SCRIPT ############################################################ | |
INSERT INTO DatabaseVersion([Version],[UpdatedBy],[UpdatedOn],[Reason]) | |
VALUES(@newVersion, @author, getdate(), @comment) | |
COMMIT TRAN | |
PRINT 'Database has been updated successfully to ' + @newVersion | |
END TRY | |
BEGIN CATCH | |
IF @@TRANCOUNT > 0 | |
BEGIN | |
ROLLBACK TRANSACTION | |
END | |
SELECT | |
ERROR_NUMBER() AS ErrorNumber, | |
ERROR_SEVERITY() AS ErrorSeverity, | |
ERROR_STATE() AS ErrorState, | |
ERROR_PROCEDURE() AS ErrorProcedure, | |
ERROR_LINE() AS ErrorLine, | |
ERROR_MESSAGE() AS ErrorMessage; | |
DECLARE @ErrorMessage NVARCHAR(max), | |
@ErrorSeverity INT, | |
@ErrorState INT; | |
SET @ErrorMessage = ERROR_MESSAGE(); | |
SET @ErrorSeverity = ERROR_SEVERITY(); | |
SET @ErrorState = ERROR_STATE(); | |
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState); | |
RETURN; | |
END CATCH; | |
ELSE | |
BEGIN | |
PRINT 'Unexpected database version - expecting: ' + @expectedVersion + 'currently: ' + @currentVersion | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment