Use ATNDBTest
Go



BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT





BEGIN TRANSACTION
GO
IF (OBJECT_ID('PAY.FK_PAY_MonthlyFactorPerEmployee_OfficePeriod') IS NOT NULL) 
Begin
	ALTER TABLE PAY.MonthlyFactorPerEmployee
	DROP CONSTRAINT FK_PAY_MonthlyFactorPerEmployee_OfficePeriod
End
GO
ALTER TABLE dbo.OfficePeriod SET (LOCK_ESCALATION = TABLE)
GO
COMMIT





BEGIN TRANSACTION
GO
IF (OBJECT_ID('PAY.FK_PAY_MonthlyFactorPerEmployee_OfficeDetail') IS NOT NULL) 
Begin
	ALTER TABLE PAY.MonthlyFactorPerEmployee
	DROP CONSTRAINT FK_PAY_MonthlyFactorPerEmployee_OfficeDetail
End
GO
ALTER TABLE dbo.OfficeDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT






BEGIN TRANSACTION
GO
IF (OBJECT_ID('PAY.FK_PAY_MonthlyFactorPerEmployee_Factor') IS NOT NULL) 
Begin
	ALTER TABLE PAY.MonthlyFactorPerEmployee
	DROP CONSTRAINT FK_PAY_MonthlyFactorPerEmployee_Factor
End
GO
ALTER TABLE PAY.Factor SET (LOCK_ESCALATION = TABLE)
GO
COMMIT





BEGIN TRANSACTION
GO
IF (OBJECT_ID('PAY.FK_PAY_MonthlyFactorPerEmployee_Employee') IS NOT NULL) 
Begin
	ALTER TABLE PAY.MonthlyFactorPerEmployee
	DROP CONSTRAINT FK_PAY_MonthlyFactorPerEmployee_Employee
End
GO
ALTER TABLE PAY.Employee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT



BEGIN TRANSACTION
GO
CREATE TABLE PAY.Tmp_MonthlyFactorPerEmployee
	(
	Serial bigint NOT NULL IDENTITY (1, 1),
	Category smallint NOT NULL,
	EmployeeRef bigint NOT NULL,
	FactorRef bigint NOT NULL,
	Value varchar(15) NULL,
	Month int NULL,
	OfficePeriodRef int NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE PAY.Tmp_MonthlyFactorPerEmployee SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT PAY.Tmp_MonthlyFactorPerEmployee ON
GO
IF EXISTS(SELECT * FROM PAY.MonthlyFactorPerEmployee)
	 EXEC('INSERT INTO PAY.Tmp_MonthlyFactorPerEmployee (Serial, EmployeeRef, FactorRef, Value, Month, OfficePeriodRef, OfficeDetailRef)
		SELECT Serial, EmployeeRef, FactorRef, Value, Month, OfficePeriodRef, OfficeDetailRef FROM PAY.MonthlyFactorPerEmployee WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT PAY.Tmp_MonthlyFactorPerEmployee OFF
GO
DROP TABLE PAY.MonthlyFactorPerEmployee
GO
EXECUTE sp_rename N'PAY.Tmp_MonthlyFactorPerEmployee', N'MonthlyFactorPerEmployee', 'OBJECT' 
GO
ALTER TABLE PAY.MonthlyFactorPerEmployee ADD CONSTRAINT
	PK_PAY_MonthlyFactorPerEmployee PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT
