Microsoft® SQL Server® 2012 Bible

1113

Chapter 49: Partitioning

49

OrderQty int NOT NULL,

UnitPrice money NOT NULL,

CarrierTrackingNumber varchar(25) NOT NULL

)

ON [PRIMARY]

GO

ALTER TABLE dbo.SalesOrderDetailUS

ADD CONSTRAINT FK_SalesOrderDetailUS_SalesOrderHeader

FOREIGN KEY (CountryRegionCode, SalesOrderID)

REFERENCES dbo.SalesOrderHeaderUS(CountryRegionCode,

SalesOrderID)

GO

ALTER TABLE dbo.SalesOrderDetailUS

ADD CONSTRAINT PK_SalesOrderDetailUS PRIMARY KEY NONCLUSTERED

(CountryRegionCode, SalesOrderDetailID)

GO

ALTER TABLE dbo.SalesOrderDetailUS

ADD CONSTRAINT

SalesOrderDetailUS_PartitionCheck CHECK

(CountryRegionCode = 'US')

GO

--move data

INSERT INTO dbo.SalesOrderHeaderUS(CountryRegionCode, SalesOrderID,

SalesOrderNumber, CustomerID, SalesPersonID, OrderDate)

SELECT

st.CountryRegionCode CountryRegionCode,

SalesOrderID,

SalesOrderNumber,

CustomerID,

SalesPersonID,

OrderDate

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

WHERE st.CountryRegionCode = 'US' AND SalesPersonID IS NOT NULL

INSERT INTO dbo.SalesOrderDetailUS(CountryRegionCode,

SalesOrderDetailID, SalesOrderID, ProductID, OrderQty,

UnitPrice, CarrierTrackingNumber)

SELECT

'US',

SalesOrderDetailID,

soh.SalesOrderID,

sod.ProductID,

sod.OrderQty,

sod.UnitPrice,

c49.indd 1113c49.indd 1113 7/31/2012 10:24:22 AM7/31/2012 10:24:22 AM

http://www.it-ebooks.info