Remove IDENTITY specification from column

removeIdentitySpecification.sql

BEGIN TRY
  -- removing IDENTITY specification from master table. Premaster table will have IDENTITY specification, and the ID will carry forward to the master table
  BEGIN TRANSACTION REMOVE_IDENTITY_SPEC

  CREATE TABLE [dbo].[MstrInvAssetPartyRoleRel_withoutIdentitySpecification] (
    [InvAssetPartyIDNum] [int] NOT NULL,
    [InvAstIdNum] [int] NOT NULL,
    [PartyRoleRelIDNum] [int] NOT NULL,
    [CADIS_SYSTEM_INSERTED] [datetime] NULL,
    [CADIS_SYSTEM_UPDATED] [datetime] NULL,
    [CADIS_SYSTEM_CHANGEDBY] [nvarchar](50) NULL
    ) ON [PRIMARY]

  IF EXISTS (
      SELECT *
      FROM dbo.MstrInvAssetPartyRoleRel
      )
    EXEC (
        'INSERT INTO MstrInvAssetPartyRoleRel_withoutIdentitySpecification (InvAssetPartyIdNum, InvAstIdNum, PartyRoleRelIdNum, CADIS_SYSTEM_INSERTED, CADIS_SYSTEM_UPDATED, CADIS_SYSTEM_CHANGEDBY)
       SELECT InvAssetPartyIdNum, InvAstIdNum, PartyRoleRelIdNum, CADIS_SYSTEM_INSERTED, CADIS_SYSTEM_UPDATED, CADIS_SYSTEM_CHANGEDBY from dbo.MstrInvAssetPartyRoleRel WITH (HOLDLOCK)'
        )

  DROP TABLE dbo.MstrInvAssetPartyRoleRel

  EXECUTE sp_rename N'dbo.MstrInvAssetPartyRoleRel_withoutIdentitySpecification',
    N'MstrInvAssetPartyRoleRel',
    'OBJECT'

  -- add the primary key constraint and default constraints
  ALTER TABLE dbo.MstrInvAssetPartyRoleRel ADD CONSTRAINT PK_MstrInvAssetPartyRoleRel PRIMARY KEY CLUSTERED (
    InvAstIdNum ASC,
    PartyRoleRelIdNum ASC
    )

  ALTER TABLE [dbo].[MstrInvAssetPartyRoleRel] ADD CONSTRAINT [DF_MstrInvAssetPartyRoleRel_INSERTED] DEFAULT(getdate())
  FOR [CADIS_SYSTEM_INSERTED]

  ALTER TABLE [dbo].[MstrInvAssetPartyRoleRel] ADD CONSTRAINT [DF_MstrInvAssetPartyRoleRel_UPDATED] DEFAULT(getdate())
  FOR [CADIS_SYSTEM_UPDATED]

  ALTER TABLE [dbo].[MstrInvAssetPartyRoleRel] ADD CONSTRAINT [DF_MstrInvAssetPartyRoleRel_CHANGEDBY] DEFAULT('UNKNOWN')
  FOR [CADIS_SYSTEM_CHANGEDBY]

  COMMIT TRANSACTION REMOVE_IDENTITY_SPEC
END TRY

BEGIN CATCH
  ROLLBACK TRANSACTION REMOVE_IDENTITY_SPEC
  DECLARE @error nvarchar(max) = 'Error ! Table: dbo.MstrInvAssetPartyRoleRel | Action: Alter | Details: ' + ERROR_MESSAGE()
  RAISERROR (@error,13,- 1)
  RETURN

END CATCH