Master Data Services (MDS) Moving Subscription Views

SET NOCOUNT ON

-------------------------------------------------------------------------------

-- Change the following variables

-------------------------------------------------------------------------------

DECLARE @Server VARCHAR(100) = 'SERVERNAME'

DECLARE @OrginalDatabase VARCHAR(100) = 'MDSDev'

DECLARE @DestinationDatabase VARCHAR(100) = 'MDSProd'

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

DECLARE @execQuery VARCHAR(1000) = 'SELECT * INTO ##Views FROM OPENROWSET(''SQLNCLI'', ''server=' + @Server + ';trusted_connection=yes'', ''set fmtonly off exec ' + @OrginalDatabase + '.[mdm].[udpSubscriptionViewGet]'')'

EXEC(@execQuery)

DECLARE @View_Name NVARCHAR(200)

DECLARE @Model_Name NVARCHAR(200)

DECLARE @Entity_Name NVARCHAR(200)

DECLARE @query NVARCHAR(4000)

DECLARE db_Cursor CURSOR FOR

SELECT View_Name, Model_Name, Entity_Name FROM ##Views

OPEN db_Cursor

FETCH NEXT FROM db_Cursor INTO @View_Name, @Model_Name, @Entity_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @query = ''

SET @query = 'DECLARE @EntityName NVARCHAR(200) = ''' + @Entity_Name + '''' + CHAR(10)

SET @query = + @query + 'DECLARE @ModelName NVARCHAR(200) = ''' + @Model_Name + '''' + CHAR(10)

SET @query = + @query + 'DECLARE @Entity_ID int' + CHAR(10)

SET @query = + @query + 'DECLARE @Model_ID int' + CHAR(10)

SET @query = + @query + 'DECLARE @ModelVersion_ID int' + CHAR(10)

SET @query = + @query + 'DECLARE @ModelVersionFlag_ID int' + CHAR(10)

SET @query = + @query + 'DECLARE @Return_ID int' + CHAR(10)

SET @query = + @query + 'DECLARE @Return_MUID uniqueidentifier' + CHAR(10)

SET @query = + @query + CHAR(10)

SET @query = + @query + 'SELECT @Model_ID = m.id, @Entity_ID=e.ID, @ModelVersion_ID = mv.ID, @ModelVersionFlag_ID = mv.VersionFlag_ID FROM mdm.tblModel m' + CHAR(10)

SET @query = + @query + 'INNER JOIN mdm.tblEntity e ON m.ID = e.Model_ID' + CHAR(10)

SET @query = + @query + 'INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID' + CHAR(10)

SET @query = + @query + 'WHERE mv.Name = ''VERSION_1''' + CHAR(10)

SET @query = + @query + 'AND m.NAME = @ModelName' + CHAR(10)

SET @query = + @query + 'AND e.Name = @EntityName' + CHAR(10)

SET @query = + @query + CHAR(10)

SET @query = + @query + 'EXECUTE [' + @DestinationDatabase + '].[mdm].[udpSubscriptionViewSave]' + CHAR(10)

SET @query = + @query + '@SubscriptionView_ID=NULL' + CHAR(10)

SET @query = + @query + ',@Entity_ID = @Entity_ID' + CHAR(10)

SET @query = + @query + ',@Model_ID = @Model_ID' + CHAR(10)

SET @query = + @query + ',@DerivedHierarchy_ID = NULL' + CHAR(10)

SET @query = + @query + ',@ModelVersion_ID = @ModelVersion_ID ' + CHAR(10)

SET @query = + @query + ',@ModelVersionFlag_ID = @ModelVersionFlag_ID' + CHAR(10)

SET @query = + @query + ',@ViewFormat_ID = 1' + CHAR(10)

SET @query = + @query + ',@Levels = NULL' + CHAR(10)

SET @query = + @query + ',@SubscriptionViewName = ''' + @View_Name + '''' + CHAR(10)

SET @query = + @query + ',@Return_ID = @Return_ID OUTPUT' + CHAR(10)

SET @query = + @query + ',@Return_MUID = @Return_MUID OUTPUT' + CHAR(10)

SET @query = + @query + 'GO' + CHAR(10)

      PRINT @query

      FETCH NEXT FROM db_Cursor INTO @View_Name, @Model_Name, @Entity_Name

END

CLOSE db_Cursor

DEALLOCATE db_Cursor

DROP TABLE ##Views

GO