103 lines
2.7 KiB
Transact-SQL
103 lines
2.7 KiB
Transact-SQL
BEGIN TRANSACTION
|
|
GO
|
|
ALTER TABLE dbo.Entities_Products
|
|
DROP CONSTRAINT FK66E1235A95976D16
|
|
GO
|
|
ALTER TABLE dbo.Entities_Products
|
|
DROP CONSTRAINT FK66E1235AEB4DC236
|
|
GO
|
|
CREATE TABLE dbo.Tmp_Entities_Products
|
|
(
|
|
ProductID int NOT NULL IDENTITY (1, 1),
|
|
Code int NULL,
|
|
Name nvarchar(100) NOT NULL,
|
|
Units nvarchar(20) NOT NULL,
|
|
ServiceCharge decimal(19, 5) NOT NULL,
|
|
IsScTaxable bit NOT NULL,
|
|
Price decimal(19, 5) NOT NULL,
|
|
FullPrice decimal(19, 5) NOT NULL,
|
|
Discontinued bit NOT NULL,
|
|
SortOrder int NULL,
|
|
ProductGroupID int NOT NULL,
|
|
ServiceTaxID int NOT NULL,
|
|
VatID int NOT NULL,
|
|
BaseCode int NOT NULL,
|
|
Quantity decimal(19, 5) NOT NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
SET IDENTITY_INSERT dbo.Tmp_Entities_Products ON
|
|
GO
|
|
IF EXISTS(SELECT * FROM dbo.Entities_Products)
|
|
EXEC('INSERT INTO dbo.Tmp_Entities_Products (ProductID, Code, Name, Units, ServiceCharge, IsScTaxable, Price, FullPrice, Discontinued, SortOrder, ProductGroupID, ServiceTaxID, VatID, BaseCode, Quantity)
|
|
SELECT ProductID, Code, Name, Units, ServiceCharge, 0, Price, FullPrice, Discontinued, SortOrder, ProductGroupID, CASE WHEN TaxID = 4 THEN 4 ELSE 3 END, TaxID, BaseCode, Quantity FROM dbo.Entities_Products WITH (HOLDLOCK TABLOCKX)')
|
|
GO
|
|
SET IDENTITY_INSERT dbo.Tmp_Entities_Products OFF
|
|
GO
|
|
ALTER TABLE dbo.Entities_Inventories
|
|
DROP CONSTRAINT FK53E4F7FE3F88CAB6
|
|
GO
|
|
DROP TABLE dbo.Entities_Products
|
|
GO
|
|
EXECUTE sp_rename N'dbo.Tmp_Entities_Products', N'Entities_Products', 'OBJECT'
|
|
GO
|
|
ALTER TABLE dbo.Entities_Products ADD CONSTRAINT
|
|
PK__Entities_Product__737017C0 PRIMARY KEY CLUSTERED
|
|
(
|
|
ProductID
|
|
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
|
|
GO
|
|
ALTER TABLE dbo.Entities_Products ADD CONSTRAINT
|
|
IX_Entities_Products_Name UNIQUE NONCLUSTERED
|
|
(
|
|
Name,
|
|
Units
|
|
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
|
|
GO
|
|
ALTER TABLE dbo.Entities_Products ADD CONSTRAINT
|
|
FK66E1235AEB4DC236 FOREIGN KEY
|
|
(
|
|
VatID
|
|
) REFERENCES dbo.Entities_Taxes
|
|
(
|
|
TaxID
|
|
) ON UPDATE NO ACTION
|
|
ON DELETE NO ACTION
|
|
|
|
GO
|
|
ALTER TABLE dbo.Entities_Products ADD CONSTRAINT
|
|
FK66E1235A95976D16 FOREIGN KEY
|
|
(
|
|
ProductGroupID
|
|
) REFERENCES dbo.Entities_ProductGroups
|
|
(
|
|
ProductGroupID
|
|
) ON UPDATE NO ACTION
|
|
ON DELETE NO ACTION
|
|
|
|
GO
|
|
ALTER TABLE dbo.Entities_Products ADD CONSTRAINT
|
|
FK_Entities_Products_Entities_Taxes FOREIGN KEY
|
|
(
|
|
ServiceTaxID
|
|
) REFERENCES dbo.Entities_Taxes
|
|
(
|
|
TaxID
|
|
) ON UPDATE NO ACTION
|
|
ON DELETE NO ACTION
|
|
|
|
GO
|
|
ALTER TABLE dbo.Entities_Inventories ADD CONSTRAINT
|
|
FK53E4F7FE3F88CAB6 FOREIGN KEY
|
|
(
|
|
ProductID
|
|
) REFERENCES dbo.Entities_Products
|
|
(
|
|
ProductID
|
|
) ON UPDATE NO ACTION
|
|
ON DELETE NO ACTION
|
|
|
|
GO
|
|
COMMIT
|