USE SQL2008SBS
GO

INSERT INTO LookupTables.AddressType
(AddressType)
VALUES('Corporate'),('Warehouse'),('Home'),('Work')
GO

SELECT AddressTypeID, AddressType
FROM LookupTables.AddressType
GO

INSERT INTO LookupTables.ProductCategory
(Category)
VALUES ('Power Tools'),('Clothing'),('Accessories')
GO

SELECT CategoryID, Category
FROM LookupTables.ProductCategory
GO

DECLARE @CategoryID     INT

SELECT @CategoryID = CategoryID
FROM LookupTables.ProductCategory
WHERE Category = 'Power Tools'

INSERT INTO LookupTables.ProductSubCategory
(CategoryID, SubcategoryName)
VALUES (@CategoryID,'Router'),(@CategoryID,'Table Saw'),
    (@CategoryID,'Drill Press'),(@CategoryID,'Belt Sander'),
    (@CategoryID,'Jointer')
GO

DECLARE @CategoryID     INT

SELECT @CategoryID = CategoryID
FROM LookupTables.ProductCategory
WHERE Category = 'Accessories'

INSERT INTO LookupTables.ProductSubCategory
(CategoryID, SubcategoryName)
VALUES (@CategoryID,'Saw Blade'),(@CategoryID,'Drill Bit'),
    (@CategoryID,'Router Bit')
GO

SELECT SubcategoryID, CategoryID, SubcategoryName
FROM LookupTables.ProductSubCategory
GO

INSERT INTO LookupTables.Country
(CountryName)
SELECT Name
FROM AdventureWorks.Person.CountryRegion
GO

SELECT CountryID, CountryName
FROM LookupTables.Country
GO

DECLARE @CountryID  INT

SELECT @CountryID = CountryID
FROM LookupTables.Country
WHERE CountryName = 'United States'

INSERT INTO LookupTables.StateProvince
(CountryID, StateProvince, StateProvinceAbbrev)
SELECT @CountryID, Name, StateProvinceCode
FROM AdventureWorks.Person.StateProvince
WHERE CountryRegionCode = 'US'
GO

DECLARE @CountryID  INT

SELECT @CountryID = CountryID
FROM LookupTables.Country
WHERE CountryName = 'Canada'

INSERT INTO LookupTables.StateProvince
(CountryID, StateProvince, StateProvinceAbbrev)
SELECT @CountryID, Name, StateProvinceCode
FROM AdventureWorks.Person.StateProvince
WHERE CountryRegionCode = 'CA'
GO

DECLARE @CountryID  INT

SELECT @CountryID = CountryID
FROM LookupTables.Country
WHERE CountryName = 'France'

INSERT INTO LookupTables.StateProvince
(CountryID, StateProvince, StateProvinceAbbrev)
SELECT @CountryID, Name, StateProvinceCode
FROM AdventureWorks.Person.StateProvince
WHERE CountryRegionCode = 'FR'
GO

DECLARE @CountryID  INT

SELECT @CountryID = CountryID
FROM LookupTables.Country
WHERE CountryName = 'Australia'

INSERT INTO LookupTables.StateProvince
(CountryID, StateProvince, StateProvinceAbbrev)
SELECT @CountryID, Name, LEFT(StateProvinceCode,2)
FROM AdventureWorks.Person.StateProvince
WHERE CountryRegionCode = 'AU'
GO

DECLARE @CountryID  INT

SELECT @CountryID = CountryID
FROM LookupTables.Country
WHERE CountryName = 'Germany'

INSERT INTO LookupTables.StateProvince
(CountryID, StateProvince, StateProvinceAbbrev)
SELECT @CountryID, Name, StateProvinceCode
FROM AdventureWorks.Person.StateProvince
WHERE CountryRegionCode = 'DE'
GO

SELECT StateProvinceID, CountryID, StateProvince, StateProvinceAbbrev
FROM LookupTables.StateProvince
GO

USE SQL2008SBS
GO

INSERT INTO Customers.Customer
(CompanyName)
VALUES('Wide World Importers')
GO

SELECT CustomerID, CompanyName, FirstName, LastName, ModifiedDate
FROM Customers.Customer
GO

DECLARE @CustomerID         INT,
        @AddressTypeID      INT,
        @StateProvinceID    INT,
        @CountryID          INT

--Retrieve IDs for Customer, Address Type, StateProvince, and Country
SELECT @CustomerID = CustomerID
FROM Customers.Customer
WHERE CompanyName = 'Wide World Importers'

SELECT @AddressTypeID = AddressTypeID
FROM LookupTables.AddressType
WHERE AddressType = 'Warehouse'

SELECT @StateProvinceID = StateProvinceID
FROM LookupTables.StateProvince
WHERE StateProvinceAbbrev = 'WA'

SELECT @CountryID = CountryID
FROM LookupTables.Country
WHERE CountryName = 'United States'

INSERT INTO Customers.CustomerAddress
(CustomerID, AddressTypeID, AddressLine1, AddressLine2, AddressLine3, 
City, StateProvinceID, CountryID)
VALUES (@CustomerID, @AddressTypeID, '123 Elm St.', NULL, NULL,
    'Seattle', @StateProvinceID, @CountryID)
GO

SELECT AddressID, CustomerID, AddressTypeID, AddressLine1, AddressLine2, 
    AddressLine3, City, StateProvinceID, CountryID
FROM Customers.CustomerAddress
GO

USE SQL2008SBS
GO

--Add the Products.ProductOptions table
CREATE TABLE Products.ProductOptions
(SKU        CHAR(10)    NOT NULL,
ProductID   INT         NOT NULL,
OptionDesc  VARCHAR(50) NOT NULL,
UnitPrice   MONEY       NOT NULL,
    CONSTRAINT pk_productoptions PRIMARY KEY CLUSTERED (SKU))
GO

ALTER TABLE Orders.OrderDetail
    ADD CONSTRAINT fk_productoptionstoorderdetail FOREIGN KEY
    (SKU) REFERENCES Products.ProductOptions (SKU)
GO

DECLARE @Subcategory    INT

SELECT @Subcategory = SubcategoryID
FROM LookupTables.ProductSubCategory
WHERE SubcategoryName = 'Router Bit'

INSERT INTO Products.Product
(SubcategoryID, ProductName, ProductCost, ListPrice)
VALUES (@Subcategory, '1/4" Shank Round Over', 2.64, 4.99),
    (@Subcategory, '1/2" Shank Round Over', 3.11, 5.99)

SELECT @Subcategory = SubcategoryID
FROM LookupTables.ProductSubCategory
WHERE SubcategoryName = 'Saw Blade'

INSERT INTO Products.Product
(SubcategoryID, ProductName, ProductCost, ListPrice)
VALUES (@Subcategory, '10"x40Tx5/8" Combination Blade', 21.58, 47.99),
    (@Subcategory, '10"x50Tx5/8 Thin Kerf Combination Blade', 22.65, 58.99)
GO

SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, 
    ProductMargin, ProductDescription
FROM Products.Product
GO

DECLARE @ProductID  INT

SELECT @ProductID = ProductID
FROM Products.Product
WHERE ProductName = '1/4" Shank Round Over'

INSERT INTO Products.ProductOptions
(SKU, ProductID, OptionDesc, UnitPrice)
VALUES('1-4RB1-2RO',@ProductID, '1/2" Radius Round Over Bit', 4.99),
('1-4RB1-4RO',@ProductID, '1/4" Radius Round Over Bit', 4.99)
GO

DECLARE @ProductID  INT

SELECT @ProductID = ProductID
FROM Products.Product
WHERE ProductName = '1/2" Shank Round Over'

INSERT INTO Products.ProductOptions
(SKU, ProductID, OptionDesc, UnitPrice)
VALUES('1-2RB1-2RO',@ProductID, '1/2" Radius Round Over Bit', 5.99),
('1-2RB1-4RO',@ProductID, '1/4" Radius Round Over Bit', 5.99),
('1-2RB3-4RO',@ProductID, '3/4" Radius Round Over Bit', 7.99)
GO

SELECT SKU, ProductID, OptionDesc, UnitPrice
FROM Products.ProductOptions
GO

USE SQL2008SBS
GO
SELECT CustomerID, CompanyName, FirstName, LastName, ModifiedDate
INTO #Customer
FROM Customers.Customer
GO

SELECT CustomerID, CompanyName, FirstName, LastName, ModifiedDate
FROM #Customer
GO

USE SQL2008SBS
GO

UPDATE Products.ProductOptions
SET UnitPrice = UnitPrice * 1.1
GO

SELECT SKU, ProductID, OptionDesc, UnitPrice
FROM Products.ProductOptions
GO

UPDATE Products.Product
SET ListPrice = ListPrice * 1.1
WHERE ListPrice BETWEEN 4.99 AND 5.99
GO

SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
FROM Products.Product
GO

UPDATE a
SET a.ProductCost = a.ProductCost * 1.3,
    a.ListPrice = a.ListPrice * 1.1
FROM Products.Product a INNER JOIN LookupTables.ProductSubCategory b ON a.SubcategoryID = b.SubcategoryID
WHERE b.SubcategoryName = 'Saw Blade'
GO

SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
FROM Products.Product
GO

UPDATE a
SET a.UnitPrice = b.ListPrice
FROM Products.ProductOptions a INNER JOIN Products.Product b ON a.ProductID = b.ProductID
WHERE b.ProductName LIKE '1/4" Shank%'
GO

SELECT SKU, ProductID, OptionDesc, UnitPrice
FROM Products.ProductOptions
GO

USE SQL2008SBS
GO

INSERT INTO Products.ProductOptions
(SKU, ProductID, OptionDesc, UnitPrice)
VALUES('123SKU',1,'Dummy description',1.99)
GO

SELECT SKU, ProductID, OptionDesc, UnitPrice
FROM Products.ProductOptions
WHERE SKU = '123SKU'
GO

DELETE FROM Products.ProductOptions
WHERE SKU = '123SKU'
GO

SELECT SKU, ProductID, OptionDesc, UnitPrice
FROM Products.ProductOptions
WHERE SKU = '123SKU'
GO

USE SQL2008SBS
GO

DECLARE @OrderID    INT

INSERT INTO Orders.OrderHeader
(CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, FinalShipDate)
VALUES(1,'7/18/2008',100.83,0,5.00,'7/18/2008')

SET @OrderID = SCOPE_IDENTITY()

INSERT INTO Orders.OrderDetail
(OrderID, SKU, Quantity, UnitPrice, ShipDate)
VALUES(@OrderID,'1-4RB1-2RO',1,4.99,'7/18/2008'),(@OrderID,'1-2RB1-2RO',16,5.99,'7/18/2008')
GO

SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Orders.OrderHeader

SELECT OrderDetailID, OrderID, SKU, Quantity, UnitPrice, ShipDate
FROM Orders.OrderDetail
GO

INSERT INTO Archive.OrderHeader
(OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, FinalShipDate)
SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, FinalShipDate
FROM Orders.OrderHeader
WHERE FinalShipDate IS NOT NULL
GO

INSERT INTO Archive.OrderDetail
(OrderDetailID, OrderID, SKU, Quantity, UnitPrice, ShipDate)
SELECT a.OrderDetailID, a.OrderID, a.SKU, a.Quantity, a.UnitPrice, a.ShipDate
FROM Orders.OrderDetail a INNER JOIN Orders.OrderHeader b ON a.OrderID = b.OrderID
WHERE b.FinalShipDate IS NOT NULL
GO

SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Archive.OrderHeader

SELECT OrderDetailID, OrderID, SKU, Quantity, UnitPrice, ShipDate
FROM Archive.OrderDetail
GO

DELETE FROM a
FROM Orders.OrderDetail a INNER JOIN Archive.OrderDetail b ON a.OrderDetailID = b.OrderDetailID
GO

DELETE FROM a
FROM Orders.OrderHeader a INNER JOIN Archive.OrderHeader b ON a.OrderID = b.OrderID
GO

SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Orders.OrderHeader

SELECT OrderDetailID, OrderID, SKU, Quantity, UnitPrice, ShipDate
FROM Orders.OrderDetail
GO

USE SQL2008SBS
GO

SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
INTO dbo.TestProduct
FROM Products.Product
GO

SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
FROM dbo.TestProduct
GO

TRUNCATE TABLE dbo.TestProduct
GO

SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
FROM dbo.TestProduct
GO

DROP TABLE dbo.TestProduct
GO

USE SQL2008SBS
GO

CREATE TABLE Products.ProductInventory
(ProductID  INT NOT NULL,
Quantity    INT NOT NULL,
CONSTRAINT pk_productinventory PRIMARY KEY CLUSTERED (ProductID))
GO

ALTER TABLE Products.ProductInventory
    ADD CONSTRAINT fk_producttoproductinventory FOREIGN KEY (ProductID)
    REFERENCES Products.Product (ProductID)
GO

CREATE TABLE Products.ProductShipment
(ProductID  INT     NOT NULL,
Quantity    INT     NOT NULL,
ReceiveDate DATE    NOT NULL CONSTRAINT df_productshipment_receivedate DEFAULT GETDATE(),
CONSTRAINT pk_productshipment PRIMARY KEY CLUSTERED (ProductID))

ALTER TABLE Products.ProductShipment
    ADD CONSTRAINT fk_producttoproductshipment FOREIGN KEY (ProductID)
    REFERENCES Products.Product (ProductID)
GO

USE SQL2008SBS
GO

INSERT INTO Products.ProductInventory
(ProductID, Quantity)
VALUES(1,100)
GO

INSERT INTO Products.ProductShipment
(ProductID, Quantity, ReceiveDate)
VALUES(1,25,GETDATE()),(2,50,GETDATE())
GO

SELECT ProductID, Quantity
FROM Products.ProductInventory

SELECT ProductID, Quantity, ReceiveDate
FROM Products.ProductShipment
GO

MERGE Products.ProductInventory AS target
USING (SELECT ProductID, Quantity
        FROM Products.ProductShipment
        WHERE ReceiveDate = CAST(GETDATE() AS DATE)) AS source
ON (target.ProductID = source.ProductID)
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity + source.Quantity
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity)
    VALUES(source.ProductID, source.Quantity);
GO

SELECT ProductID, Quantity
FROM Products.ProductInventory

SELECT ProductID, Quantity, ReceiveDate
FROM Products.ProductShipment
GO

ALTER DATABASE SQL2008SBS
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON)
GO

USE SQL2008SBS
GO

ALTER TABLE HumanResources.Employee
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION()
GO

INSERT INTO HumanResources.Employee
(FirstName, LastName, JobTitle, BirthDate, HireDate)
VALUES('Dan','Park','Engineer','1/1/1972','3/7/1994')
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION()
GO

--NULL returns the first change version
--Specifying a version number return all changes since that version
SELECT a.EmployeeID, a.SYS_CHANGE_VERSION, a.SYS_CHANGE_CREATION_VERSION, a.SYS_CHANGE_OPERATION,
a.SYS_CHANGE_COLUMNS, a.SYS_CHANGE_CONTEXT
FROM CHANGETABLE(CHANGES HumanResources.Employee, NULL) AS a

SELECT a.EmployeeID, a.SYS_CHANGE_VERSION, a.SYS_CHANGE_CREATION_VERSION, a.SYS_CHANGE_OPERATION,
a.SYS_CHANGE_COLUMNS, a.SYS_CHANGE_CONTEXT
FROM CHANGETABLE(CHANGES HumanResources.Employee, 0) AS a
GO

UPDATE HumanResources.Employee
SET HireDate = '4/7/1996'
WHERE FirstName = 'Dan' AND LastName = 'Park'
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION()
GO

SELECT a.EmployeeID, a.SYS_CHANGE_VERSION, a.SYS_CHANGE_CREATION_VERSION, a.SYS_CHANGE_OPERATION,
a.SYS_CHANGE_COLUMNS, a.SYS_CHANGE_CONTEXT, 
    CASE WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK
        (COLUMNPROPERTY(OBJECT_ID('HumanResources.Employee'), 'HireDate', 'ColumnId')
            ,a.SYS_CHANGE_COLUMNS) = 1 THEN 'ColumnChanged' ELSE 'ColumnNotChanged' END HireDateChanged
FROM CHANGETABLE(CHANGES HumanResources.Employee, 1) AS a
GO

ALTER TABLE HumanResources.Employee
DISABLE CHANGE_TRACKING
GO

ALTER DATABASE SQL2008SBS
SET CHANGE_TRACKING = OFF
GO

ALTER DATABASE SQL2008SBS
    ADD FILEGROUP CDC
GO

ALTER DATABASE SQL2008SBS
    ADD FILE (NAME = CDC, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CDC.ndf')
    TO FILEGROUP CDC
GO

USE SQL2008SBS
GO

EXEC sys.sp_cdc_enable_db
GO

EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name   = N'Employee',
@role_name     = NULL,
@filegroup_name = N'CDC',
@capture_instance = N'HumanResources_Employee',
@supports_net_changes = 1
GO

INSERT INTO HumanResources.Employee
(FirstName, LastName, JobTitle, BirthDate, HireDate)
VALUES ('Kim','Akers','Engineer','1/1/1968','3/6/2000')
GO

SELECT EmployeeID, FirstName, LastName, JobTitle, BirthDate, HireDate
FROM HumanResources.Employee
GO

DECLARE @from_lsn binary(10), 
        @to_lsn binary(10)
        
SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Employee')
SET @to_lsn   = sys.fn_cdc_get_max_lsn()

SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Employee
  (@from_lsn, @to_lsn, N'all')
GO

UPDATE HumanResources.Employee
SET HireDate = '4/6/2000'
WHERE FirstName = 'Kim' AND LastName = 'Akers'
GO

SELECT EmployeeID, FirstName, LastName, JobTitle, BirthDate, HireDate
FROM HumanResources.Employee
GO

DECLARE @from_lsn binary(10), 
        @to_lsn binary(10)
        
SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Employee')
SET @to_lsn   = sys.fn_cdc_get_max_lsn()

SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Employee
  (@from_lsn, @to_lsn, N'all')
GO

EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Employee',
@capture_instance = N'HumanResources_Employee'
GO

EXEC sys.sp_cdc_disable_db
GO