USE [MSSQL_E13]
GO
CREATE SCHEMA [raw]
GO

IF OBJECT_ID (N'[raw].[Example_Hub]', N'U') IS NOT NULL
    DROP TABLE [raw].[Example_Hub];
GO

CREATE TABLE [raw].[Example_Hub] (
     [Example_HK] CHAR(32) NOT NULL -- the hashkey
    ,[Example_BK] NVARCHAR(10) NOT NULL -- the business key
    ,[SA_SourceSystem_ID] SMALLINT NOT NULL
    ,[DW_Load_ID] BIGINT NOT NULL
    ,[DW_Sequence_ID] INT NOT NULL -- don't add IDENTITY(1,1) here,
                                   -- the alternative is below.
    ,CONSTRAINT [PK_Example_Hub] PRIMARY KEY NONCLUSTERED (
          [Example_HK] ASC -- due to the distributed character of
          -- the hashkey, it is created as NONCLUSTERED.
    )
    ,CONSTRAINT [IX_UN_Example_Hub] UNIQUE (
          [Example_BK]
    )
    ,CONSTRAINT [IX_UC_Example_Hub] UNIQUE CLUSTERED (
          [DW_Sequence_ID] -- the additional sequence number is
          -- created with a UNIQUE CLUSTERED index.
    )
);
GO
-- Create a sequence to use instead of IDENTITY.
-- Note that the sequence is not bound to the table.
-- It is in our case only related to the table using a naming
-- convention {TABLENAME}_Sequence, but you could use a sequence
-- to be used by multiple tables, although I do not know why you
-- would want that (it could become a bottleneck for insertions).
IF OBJECT_ID('[raw].[Example_Hub_Sequence]', 'SO') IS NULL
    CREATE SEQUENCE [raw].[Example_Hub_Sequence] AS INT START WITH 1;

-- Add default constraint for column [DW_Sequence_ID] so that
-- it uses the next value of the sequence.
ALTER TABLE [raw].[Example_Hub]
    ADD CONSTRAINT [DF_raw_Example_Hub__DW_Sequence_ID]
    DEFAULT NEXT VALUE FOR [raw].[Example_Hub_Sequence]
    FOR [DW_Sequence_ID];
GO

--\
---) Keep ExampleId's
---) Obviously it only makes sense to run this script when
---) (generated) values are loaded to the table and the [DW_Sequence_ID]
---) column, that you now want to change.
--/
BEGIN TRY
    BEGIN TRAN;

    --\
    ---) Swap the sign of all ID's to a negative value.
    ---) This works only if all values are positive.
    --/
    UPDATE hub
      SET [DW_Sequence_ID] = -1 * [DW_Sequence_ID]
      FROM [raw].[Example_Hub] hub
      WHERE [DW_Sequence_ID] > 0;

    --\
    ---) Now update the values.
    --/
    UPDATE hub
      SET [DW_Sequence_ID] = curdb.ExampleId
      FROM [raw].[Example_Hub] hub
      JOIN [ExistingDB].[dbo].[dim_Example] curdb
        ON curdb.ExampleCode = hub.Example_Code_BK

    --\
    ---) Make sure that the sequence next value will be higher than the
    ---) already used ID's in the existing table.
    --/
    DECLARE @MaxID_New INT = ISNULL((SELECT MAX([DW_Sequence_ID]) FROM [raw].[Example_Hub]), 0) + 1;
    DECLARE @sql VARCHAR(1000)

    SET @sql = 'ALTER SEQUENCE [raw].[Example_Hub_Sequence] RESTART WITH ' + CONVERT(VARCHAR, @MaxID_New) + ';'
    PRINT @sql
    EXEC(@sql);

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;
    THROW;
END CATCH

--\
---) If not all values are updated, you have to check manually.
---) Maybe you can just swap the sign again to make it a positive value, if that
---) does not conflict with existing values.
--/
IF EXISTS(SELECT 1 FROM [raw].[Example_Hub] hub WHERE [DW_Sequence_ID] < 0)
BEGIN
    PRINT 'Not all rows of [Example_Hub] are updated, please check.'
    SELECT * FROM [raw].[Example_Hub] hub WHERE [DW_Sequence_ID] < 0;
END

GO