The script below is necessary to run before using the database tool that updates Alarm Biller to Managely.
/*********************************************************
* IT'S A REALLY GOOD IDEA TO WRAP THIS IN A TRANSACTION *
*********************************************************/
-- Create a new table with a new Id column
SELECT Id = IDENTITY(INT, 1, 1), aChargesGroup.*
INTO aChargesGroup2
FROM aChargesGroup
ORDER BY CreatedDate
-- Add new FK Columns
ALTER TABLE aEstimateItem
ADD ChargesGroupId2 INT
ALTER TABLE aEstimatePart
ADD ChargesGroupId2 INT
ALTER TABLE aInvoiceDetail
ADD ChargesGroupId2 INT
ALTER TABLE aWorkOrderItem
ADD ChargesGroupId2 INT
ALTER TABLE aWorkOrderPart
ADD ChargesGroupId2 INT
ALTER TABLE sa_aSalesPackageItem
ADD ChargesGroupId2 INT
ALTER TABLE sa_aSalesPackagePart
ADD ChargesGroupId2 INT
-- Populate column
UPDATE aEstimateItem
SET aEstimateItem.ChargesGroupId2 = aChargesGroup2.Id
FROM aChargesGroup2
WHERE aEstimateItem.ChargesGroupId = aChargesGroup2.ChargesGroupId
UPDATE aEstimatePart
SET aEstimatePart.ChargesGroupId2 = aChargesGroup2.Id
FROM aChargesGroup2
WHERE aEstimatePart.ChargesGroupId = aChargesGroup2.ChargesGroupId
UPDATE aInvoiceDetail
SET aInvoiceDetail.ChargesGroupId2 = aChargesGroup2.Id
FROM aChargesGroup2
WHERE aInvoiceDetail.ChargesGroupId = aChargesGroup2.ChargesGroupId
UPDATE aWorkOrderItem
SET aWorkOrderItem.ChargesGroupId2 = aChargesGroup2.Id
FROM aChargesGroup2
WHERE aWorkOrderItem.ChargesGroupId = aChargesGroup2.ChargesGroupId
UPDATE aWorkOrderPart
SET aWorkOrderPart.ChargesGroupId2 = aChargesGroup2.Id
FROM aChargesGroup2
WHERE aWorkOrderPart.ChargesGroupId = aChargesGroup2.ChargesGroupId
UPDATE sa_aSalesPackageItem
SET sa_aSalesPackageItem.ChargesGroupId2 = aChargesGroup2.Id
FROM aChargesGroup2
WHERE sa_aSalesPackageItem.ChargesGroupId = aChargesGroup2.ChargesGroupId
UPDATE sa_aSalesPackagePart
SET sa_aSalesPackagePart.ChargesGroupId2 = aChargesGroup2.Id
FROM aChargesGroup2
WHERE sa_aSalesPackagePart.ChargesGroupId = aChargesGroup2.ChargesGroupId
-- Delete Old Id
ALTER TABLE aChargesGroup2
DROP COLUMN ChargesGroupId
-- Delete Relationships
ALTER TABLE aEstimateItem DROP CONSTRAINT IF EXISTS [FK_aEstimateItem_aChargesGroup]
ALTER TABLE aEstimatePart DROP CONSTRAINT IF EXISTS [FK_aEstimatePart_aChargesGroup]
ALTER TABLE aInvoiceDetail DROP CONSTRAINT IF EXISTS [FK_aInvoiceDetail_aChargesGroup]
ALTER TABLE aWorkOrderItem DROP CONSTRAINT IF EXISTS [FK_aWorkOrderItem_aChargesGroup]
ALTER TABLE aWorkOrderPart DROP CONSTRAINT IF EXISTS [FK_aWorkOrderPart_aChargesGroup]
ALTER TABLE sa_aSalesPackageItem DROP CONSTRAINT IF EXISTS [FK_sa_aSalesPackageItem_aChargesGroup]
ALTER TABLE sa_aSalesPackagePart DROP CONSTRAINT IF EXISTS [FK_sa_aSalesPackagePart_aChargesGroup]
ALTER TABLE aChargesGroup DROP CONSTRAINT IF EXISTS PK_ChargesGroupId
-- Create Relationships
ALTER TABLE aChargesGroup2
ADD CONSTRAINT [PK_aChargesGroup]
PRIMARY KEY CLUSTERED([Id] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90
) ON [PRIMARY]
ALTER TABLE aEstimateItem WITH CHECK ADD CONSTRAINT [FK_aEstimateItem_aChargesGroup] FOREIGN KEY([ChargesGroupId2])
REFERENCES aChargesGroup2 ([Id])
ON DELETE CASCADE
ALTER TABLE aEstimateItem CHECK CONSTRAINT [FK_aEstimateItem_aChargesGroup]
ALTER TABLE aEstimatePart WITH CHECK ADD CONSTRAINT [FK_aEstimatePart_aChargesGroup] FOREIGN KEY([ChargesGroupId2])
REFERENCES aChargesGroup2 ([Id])
ON DELETE CASCADE
ALTER TABLE aEstimatePart CHECK CONSTRAINT [FK_aEstimatePart_aChargesGroup]
ALTER TABLE aInvoiceDetail WITH CHECK ADD CONSTRAINT [FK_aInvoiceDetail_aChargesGroup] FOREIGN KEY([ChargesGroupId2])
REFERENCES aChargesGroup2 ([Id])
ON DELETE CASCADE
ALTER TABLE aInvoiceDetail CHECK CONSTRAINT [FK_aInvoiceDetail_aChargesGroup]
ALTER TABLE aWorkOrderItem WITH CHECK ADD CONSTRAINT [FK_aWorkOrderItem_aChargesGroup] FOREIGN KEY([ChargesGroupId2])
REFERENCES aChargesGroup2 ([Id])
ON DELETE CASCADE
ALTER TABLE aWorkOrderItem CHECK CONSTRAINT [FK_aWorkOrderItem_aChargesGroup]
ALTER TABLE aWorkOrderPart WITH CHECK ADD CONSTRAINT [FK_aWorkOrderPart_aChargesGroup] FOREIGN KEY([ChargesGroupId2])
REFERENCES aChargesGroup2 ([Id])
ON DELETE CASCADE
ALTER TABLE aWorkOrderPart CHECK CONSTRAINT [FK_aWorkOrderPart_aChargesGroup]
ALTER TABLE sa_aSalesPackageItem WITH CHECK ADD CONSTRAINT [FK_sa_aSalesPackageItem_aChargesGroup] FOREIGN KEY([ChargesGroupId2])
REFERENCES aChargesGroup2 ([Id])
ON DELETE CASCADE
ALTER TABLE sa_aSalesPackageItem CHECK CONSTRAINT [FK_sa_aSalesPackageItem_aChargesGroup]
ALTER TABLE sa_aSalesPackagePart WITH CHECK ADD CONSTRAINT [FK_sa_aSalesPackagePart_aChargesGroup] FOREIGN KEY([ChargesGroupId2])
REFERENCES aChargesGroup2 ([Id])
ON DELETE CASCADE
ALTER TABLE sa_aSalesPackagePart CHECK CONSTRAINT [FK_sa_aSalesPackagePart_aChargesGroup]
-- Rename Columns
EXEC sp_RENAME 'aEstimateItem.ChargesGroupId', 'OldChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aEstimateItem.ChargesGroupId2', 'ChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aEstimatePart.ChargesGroupId', 'OldChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aEstimatePart.ChargesGroupId2', 'ChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aInvoiceDetail.ChargesGroupId', 'OldChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aInvoiceDetail.ChargesGroupId2', 'ChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aWorkOrderItem.ChargesGroupId', 'OldChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aWorkOrderItem.ChargesGroupId2', 'ChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aWorkOrderPart.ChargesGroupId', 'OldChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'aWorkOrderPart.ChargesGroupId2', 'ChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'sa_aSalesPackageItem.ChargesGroupId', 'OldChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'sa_aSalesPackageItem.ChargesGroupId2', 'ChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'sa_aSalesPackagePart.ChargesGroupId', 'OldChargesGroupId', 'COLUMN'
EXEC sp_RENAME 'sa_aSalesPackagePart.ChargesGroupId2', 'ChargesGroupId', 'COLUMN'
-- Rename Tables
EXEC sp_rename aChargesGroup, aChargesGroupOld
EXEC sp_rename aChargesGroup2, aChargesGroup
