SummerOfTech_DB_Create.sql

/*
Deployment script for SummerOfTech2

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "SummerOfTech"
:setvar DefaultFilePrefix "SummerOfTech"
:setvar DefaultDataPath "C:\MSSQL\DATA\"
:setvar DefaultLogPath "C:\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [master];


GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY SIMPLE,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT ON 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY CHECKSUM,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET HONOR_BROKER_PRIORITY OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
ALTER DATABASE [$(DatabaseName)]
    SET TARGET_RECOVERY_TIME = 0 SECONDS 
    WITH ROLLBACK IMMEDIATE;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
                CONTAINMENT = NONE 
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)];


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO
PRINT N'Creating [dbo].[Address]...';


GO
CREATE TABLE [dbo].[Address] (
    [AddressID]    INT           IDENTITY (1, 1) NOT NULL,
    [StreetNumber] NVARCHAR (10) NULL,
    [AddressLine1] NVARCHAR (50) NULL,
    [AddressLine2] NVARCHAR (50) NULL,
    [City]         NVARCHAR (50) NOT NULL,
    [Region]       NVARCHAR (50) NULL,
    [PostCode]     NVARCHAR (10) NULL,
    [Country]      NVARCHAR (50) NULL,
    CONSTRAINT [PK_dbo_Address] PRIMARY KEY CLUSTERED ([AddressID] ASC)
);


GO
PRINT N'Creating [dbo].[Customer]...';


GO
CREATE TABLE [dbo].[Customer] (
    [CustomerID] INT           IDENTITY (1, 1) NOT NULL,
    [FirstName]  NVARCHAR (50) NOT NULL,
    [LastName]   NVARCHAR (50) NOT NULL,
    [AddressID]  INT           NOT NULL,
    CONSTRAINT [PK_dbo_Person] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);


GO
PRINT N'Creating [dbo].[OrderHeader]...';


GO
CREATE TABLE [dbo].[OrderHeader] (
    [OrderID]       INT   IDENTITY (1, 1) NOT NULL,
    [DateSold]      DATE  NOT NULL,
    [CustomerID]    INT   NOT NULL,
    [SalesRegionID] INT   NOT NULL,
    [DateShipped]   DATE  NULL,
    [SaleTotal]     MONEY NOT NULL,
    CONSTRAINT [PK_dbo_Sales] PRIMARY KEY NONCLUSTERED ([OrderID] ASC)
);


GO
PRINT N'Creating [dbo].[OrderHeader].[CIX_dbo_OrderHeader]...';


GO
CREATE CLUSTERED INDEX [CIX_dbo_OrderHeader]
    ON [dbo].[OrderHeader]([DateSold] ASC);


GO
PRINT N'Creating [dbo].[OrderHeader].[IX_dbo_OrderHeader_CustomerID]...';


GO
CREATE NONCLUSTERED INDEX [IX_dbo_OrderHeader_CustomerID]
    ON [dbo].[OrderHeader]([CustomerID] ASC);


GO
PRINT N'Creating [dbo].[OrderHeader].[IX_dbo_OrderHeader_SalesRegionID]...';


GO
CREATE NONCLUSTERED INDEX [IX_dbo_OrderHeader_SalesRegionID]
    ON [dbo].[OrderHeader]([SalesRegionID] ASC);


GO
PRINT N'Creating [dbo].[OrderHeader].[IX_dbo_OrderHeader_DateShipped]...';


GO
CREATE NONCLUSTERED INDEX [IX_dbo_OrderHeader_DateShipped]
    ON [dbo].[OrderHeader]([DateShipped] ASC);


GO
PRINT N'Creating [dbo].[OrderLines]...';


GO
CREATE TABLE [dbo].[OrderLines] (
    [OrderLineID] INT      IDENTITY (1, 1) NOT NULL,
    [OrderID]     INT      NOT NULL,
    [ProductID]   INT      NOT NULL,
    [Price]       MONEY    NOT NULL,
    [Qty]         SMALLINT NOT NULL,
    [TaxPct]      MONEY    NOT NULL,
    [LineTotal]   AS       CAST ((Price * Qty) * (1 + (TaxPct / 100)) AS MONEY),
    CONSTRAINT [PK_dbo_OrderLines] PRIMARY KEY NONCLUSTERED ([OrderLineID] ASC)
);


GO
PRINT N'Creating [dbo].[OrderLines].[CIX_dbo_OrderLines]...';


GO
CREATE CLUSTERED INDEX [CIX_dbo_OrderLines]
    ON [dbo].[OrderLines]([OrderID] ASC);


GO
PRINT N'Creating [dbo].[OrderLines].[IX_dbo_OrderLines]...';


GO
CREATE NONCLUSTERED INDEX [IX_dbo_OrderLines]
    ON [dbo].[OrderLines]([ProductID] ASC);


GO
PRINT N'Creating [dbo].[ProductCategory]...';


GO
CREATE TABLE [dbo].[ProductCategory] (
    [ProductCategoryID]   INT           IDENTITY (1, 1) NOT NULL,
    [CategoryName]        NVARCHAR (50) NULL,
    [ProductCategoryCode] NCHAR (10)    NULL,
    CONSTRAINT [PK_dbo_ProductGroup] PRIMARY KEY CLUSTERED ([ProductCategoryID] ASC),
    CONSTRAINT [AK_dbo_ProductCategory_CategoryName] UNIQUE NONCLUSTERED ([CategoryName] ASC)
);


GO
PRINT N'Creating [dbo].[Products]...';


GO
CREATE TABLE [dbo].[Products] (
    [ProductID]         INT             IDENTITY (1, 1) NOT NULL,
    [ProductName]       NVARCHAR (50)   NOT NULL,
    [Colour]            NVARCHAR (15)   NULL,
    [WeightKG]          NUMERIC (10, 3) NULL,
    [Cost]              MONEY           NULL,
    [ProductCategoryID] INT             NOT NULL,
    [ProductCode]       CHAR (15)       NOT NULL,
    CONSTRAINT [PK_dbo_Products] PRIMARY KEY CLUSTERED ([ProductID] ASC),
    CONSTRAINT [AK_dbo_Products_ProductCode] UNIQUE NONCLUSTERED ([ProductCode] ASC)
);


GO
PRINT N'Creating [dbo].[Products].[IX_dbo_Products_ProductCategoryID]...';


GO
CREATE NONCLUSTERED INDEX [IX_dbo_Products_ProductCategoryID]
    ON [dbo].[Products]([ProductCategoryID] ASC);


GO
PRINT N'Creating [dbo].[SalesRegion]...';


GO
CREATE TABLE [dbo].[SalesRegion] (
    [SalesRegionID] INT           IDENTITY (1, 1) NOT NULL,
    [RegionName]    NVARCHAR (50) NOT NULL,
    [AreaSqKM]      INT           NOT NULL,
    [Population]    INT           NOT NULL,
    [ISOCode]       CHAR (6)      NOT NULL,
    CONSTRAINT [PK_dbo_SalesRegion] PRIMARY KEY CLUSTERED ([SalesRegionID] ASC),
    CONSTRAINT [AK_dbo_SalesRegion_ISOCode] UNIQUE NONCLUSTERED ([ISOCode] ASC)
);


GO
PRINT N'Creating FK_dbo_Person_dbo_Address...';


GO
ALTER TABLE [dbo].[Customer]
    ADD CONSTRAINT [FK_dbo_Person_dbo_Address] FOREIGN KEY ([AddressID]) REFERENCES [dbo].[Address] ([AddressID]);


GO
PRINT N'Creating FK_dbo_Sales_dbo_SalesRegion...';


GO
ALTER TABLE [dbo].[OrderHeader]
    ADD CONSTRAINT [FK_dbo_Sales_dbo_SalesRegion] FOREIGN KEY ([SalesRegionID]) REFERENCES [dbo].[SalesRegion] ([SalesRegionID]);


GO
PRINT N'Creating FK_dbo_Products_dbo_ProductGroup...';


GO
ALTER TABLE [dbo].[Products]
    ADD CONSTRAINT [FK_dbo_Products_dbo_ProductGroup] FOREIGN KEY ([ProductCategoryID]) REFERENCES [dbo].[ProductCategory] ([ProductCategoryID]);


GO
-- Refactoring step to update target server with deployed transaction logs

IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
BEGIN
    CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
    EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
END
GO
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '7d086027-2947-4ac5-9d20-cb72867f0144')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('7d086027-2947-4ac5-9d20-cb72867f0144')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'ac1da7c7-a06a-4c8f-a019-5292864eac25')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('ac1da7c7-a06a-4c8f-a019-5292864eac25')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'c84eff74-ff0f-44f1-9167-80c0e99f078d')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('c84eff74-ff0f-44f1-9167-80c0e99f078d')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '2fcf631d-d689-47eb-a298-609400961364')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('2fcf631d-d689-47eb-a298-609400961364')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '4b607af2-0156-4d27-ac58-17010f75b05f')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('4b607af2-0156-4d27-ac58-17010f75b05f')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '5d837b4a-8f87-4fba-8c0a-679e15ad1066')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('5d837b4a-8f87-4fba-8c0a-679e15ad1066')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'c4e2efce-b136-4852-9d03-e6bf27cd50de')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('c4e2efce-b136-4852-9d03-e6bf27cd50de')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'a9d4840b-0d7f-4b5b-99ca-71a6696af355')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('a9d4840b-0d7f-4b5b-99ca-71a6696af355')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '419dca2b-5f41-4f18-92da-3810f40d882c')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('419dca2b-5f41-4f18-92da-3810f40d882c')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '729b53bc-13bf-42d0-9616-90d76599520b')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('729b53bc-13bf-42d0-9616-90d76599520b')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'ce0c4b99-91f9-4ad4-adc7-c8e4f4033001')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('ce0c4b99-91f9-4ad4-adc7-c8e4f4033001')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '6e2efc4e-b374-48f6-8d1d-6dd619279453')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('6e2efc4e-b374-48f6-8d1d-6dd619279453')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '3f53eb22-0619-4505-b4ac-ba8fc3dc0931')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('3f53eb22-0619-4505-b4ac-ba8fc3dc0931')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'c9d1b29b-a80a-4577-a5e3-b40d2c013882')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('c9d1b29b-a80a-4577-a5e3-b40d2c013882')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'fdca4579-39d8-4a63-8e0f-0e4b360787c5')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('fdca4579-39d8-4a63-8e0f-0e4b360787c5')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '6982fbad-76b4-444a-a037-cd8aa11085ab')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('6982fbad-76b4-444a-a037-cd8aa11085ab')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '21ea5536-2445-4714-9645-f78d8168527f')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('21ea5536-2445-4714-9645-f78d8168527f')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '3dce4d64-91c8-4944-a340-b69f11f09d62')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('3dce4d64-91c8-4944-a340-b69f11f09d62')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '7c44f0b8-71e0-472c-b9f6-1a8ed774825f')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('7c44f0b8-71e0-472c-b9f6-1a8ed774825f')

GO

GO

SET IDENTITY_INSERT dbo.SalesRegion ON;
GO

WITH Regions(RegionID, RegionName, AreaSqKm, [Population], ISOCode) AS (

	SELECT 1, 'Northland', 13941, 158700, 'NZ-NTL' UNION ALL
	SELECT 2, 'Auckland', 5600, 1529300, 'NZ-AUK' UNION ALL
	SELECT 3, 'Waikato', 25598, 418500, 'NZ-WKO' UNION ALL
	SELECT 4, 'Bay of Plenty', 12447, 278100, 'NZ-BOP' UNION ALL
	SELECT 5, 'Gisborne', 8351, 46700, 'NZ-GIS' UNION ALL
	SELECT 6, 'Hawke''s Bay', 14164, 155000, 'NZ-HKB' UNION ALL
	SELECT 7, 'Taranaki', 7273, 110500, 'NZ-TKI' UNION ALL
	SELECT 8, 'Manawatu-Whanganui', 22215, 232700, 'NZ-MWT' UNION ALL
	SELECT 9, 'Wellington', 8124, 492500, 'NZ-WGN' UNION ALL
	SELECT 10, 'Tasman', 9786, 48600, 'NZ-TAS' UNION ALL
	SELECT 11, 'Nelson', 445, 46800, 'NZ-NSN' UNION ALL
	SELECT 12, 'Marlborough', 12484, 45900, 'NZ-MBH' UNION ALL
	SELECT 13, 'West Coast', 23336, 32700, 'NZ-WTC' UNION ALL
	SELECT 14, 'Canterbury', 45346, 566000, 'NZ-CAN' UNION ALL
	SELECT 15, 'Otago', 31990, 213200, 'NZ-OTA' UNION ALL
	SELECT 16, 'Southland', 34347, 94800, 'NZ-STL'
)
MERGE INTO dbo.SalesRegion AS tgt
USING Regions AS src
ON src.RegionID = tgt.SalesRegionID

WHEN MATCHED THEN UPDATE
SET RegionName = src.RegionName, AreaSqKm = src.AreaSqKm, [Population] = src.[Population], ISOCode = src.ISOCode

WHEN NOT MATCHED THEN INSERT(SalesRegionID, RegionName, AreaSqKm, [Population], [ISOCode])
VALUES(src.RegionID, src.RegionName, src.AreaSqKm, src.[Population], src.[ISOCode]);
GO
SET IDENTITY_INSERT dbo.SalesRegion OFF;
SET IDENTITY_INSERT dbo.ProductCategory ON;
GO

WITH cProductCategory(ProductCategoryID, CategoryName, ProductCategoryCode)AS (
	SELECT 1,	'Bikes',			'CFBDA25C'	UNION ALL
	SELECT 2,	'Components',		'C657828D'	UNION ALL
	SELECT 3,	'Clothing',			'10A7C342'	UNION ALL
	SELECT 4,	'Accessories',		'2BE3BE36'
)
MERGE INTO dbo.ProductCategory AS tgt
USING cProductCategory AS src
ON tgt.ProductCategoryID = src.ProductCategoryID

WHEN MATCHED THEN UPDATE
SET CategoryName = src.CategoryName, ProductCategoryCode = src.ProductCategoryCode

WHEN NOT MATCHED THEN INSERT(ProductCategoryID, CategoryName, ProductCategoryCode)
VALUES(src.ProductCategoryID, CategoryName, ProductCategoryCode)
;
GO
SET IDENTITY_INSERT dbo.ProductCategory OFF;
GO
SET IDENTITY_INSERT dbo.Products ON;
GO
WITH cProducts(ProductID, ProductName, Colour, WeightKG, Cost, ProductCode, ProductCategoryID)  AS (
SELECT        680, 'HL Road Frame - Black, 58', 'Black',          2,       1059, 'FR-R92B-58',          2 UNION ALL
SELECT        706, 'HL Road Frame - Red, 58', 'Red',          2,       1059, 'FR-R92R-58',          2 UNION ALL
SELECT        707, 'Sport-100 Helmet, Red', 'Red', NULL,         13, 'HL-U509-R',          4 UNION ALL
SELECT        708, 'Sport-100 Helmet, Black', 'Black', NULL,         13, 'HL-U509',          4 UNION ALL
SELECT        709, 'Mountain Bike Socks, M', 'White', NULL,          3, 'SO-B909-M',          3 UNION ALL
SELECT        710, 'Mountain Bike Socks, L', 'White', NULL,          3, 'SO-B909-L',          3 UNION ALL
SELECT        711, 'Sport-100 Helmet, Blue', 'Blue', NULL,         13, 'HL-U509-B',          4 UNION ALL
SELECT        712, 'AWC Logo Cap', 'Multi', NULL,          7, 'CA-1098',          3 UNION ALL
SELECT        713, 'Long-Sleeve Logo Jersey, S', 'Multi', NULL,         38, 'LJ-0192-S',          3 UNION ALL
SELECT        714, 'Long-Sleeve Logo Jersey, M', 'Multi', NULL,         38, 'LJ-0192-M',          3 UNION ALL
SELECT        715, 'Long-Sleeve Logo Jersey, L', 'Multi', NULL,         38, 'LJ-0192-L',          3 UNION ALL
SELECT        716, 'Long-Sleeve Logo Jersey, XL', 'Multi', NULL,         38, 'LJ-0192-X',          3 UNION ALL
SELECT        717, 'HL Road Frame - Red, 62', 'Red',          2,        869, 'FR-R92R-62',          2 UNION ALL
SELECT        718, 'HL Road Frame - Red, 44', 'Red',          2,        869, 'FR-R92R-44',          2 UNION ALL
SELECT        719, 'HL Road Frame - Red, 48', 'Red',          2,        869, 'FR-R92R-48',          2 UNION ALL
SELECT        720, 'HL Road Frame - Red, 52', 'Red',          2,        869, 'FR-R92R-52',          2 UNION ALL
SELECT        721, 'HL Road Frame - Red, 56', 'Red',          2,        869, 'FR-R92R-56',          2 UNION ALL
SELECT        722, 'LL Road Frame - Black, 58', 'Black',          2,        205, 'FR-R38B-58',          2 UNION ALL
SELECT        723, 'LL Road Frame - Black, 60', 'Black',          2,        205, 'FR-R38B-60',          2 UNION ALL
SELECT        724, 'LL Road Frame - Black, 62', 'Black',          3,        205, 'FR-R38B-62',          2 UNION ALL
SELECT        725, 'LL Road Frame - Red, 44', 'Red',          2,        187, 'FR-R38R-44',          2 UNION ALL
SELECT        726, 'LL Road Frame - Red, 48', 'Red',          2,        187, 'FR-R38R-48',          2 UNION ALL
SELECT        727, 'LL Road Frame - Red, 52', 'Red',          2,        187, 'FR-R38R-52',          2 UNION ALL
SELECT        728, 'LL Road Frame - Red, 58', 'Red',          2,        187, 'FR-R38R-58',          2 UNION ALL
SELECT        729, 'LL Road Frame - Red, 60', 'Red',          2,        187, 'FR-R38R-60',          2 UNION ALL
SELECT        730, 'LL Road Frame - Red, 62', 'Red',          3,        187, 'FR-R38R-62',          2 UNION ALL
SELECT        731, 'ML Road Frame - Red, 44', 'Red',          2,        352, 'FR-R72R-44',          2 UNION ALL
SELECT        732, 'ML Road Frame - Red, 48', 'Red',          2,        352, 'FR-R72R-48',          2 UNION ALL
SELECT        733, 'ML Road Frame - Red, 52', 'Red',          2,        352, 'FR-R72R-52',          2 UNION ALL
SELECT        734, 'ML Road Frame - Red, 58', 'Red',          2,        352, 'FR-R72R-58',          2 UNION ALL
SELECT        735, 'ML Road Frame - Red, 60', 'Red',          2,        352, 'FR-R72R-60',          2 UNION ALL
SELECT        736, 'LL Road Frame - Black, 44', 'Black',          2,        205, 'FR-R38B-44',          2 UNION ALL
SELECT        737, 'LL Road Frame - Black, 48', 'Black',          2,        205, 'FR-R38B-48',          2 UNION ALL
SELECT        738, 'LL Road Frame - Black, 52', 'Black',          2,        205, 'FR-R38B-52',          2 UNION ALL
SELECT        739, 'HL Mountain Frame - Silver, 42', 'Silver',          3,        747, 'FR-M94S-42',          2 UNION ALL
SELECT        740, 'HL Mountain Frame - Silver, 44', 'Silver',          3,        707, 'FR-M94S-44',          2 UNION ALL
SELECT        741, 'HL Mountain Frame - Silver, 48', 'Silver',          3,        707, 'FR-M94S-52',          2 UNION ALL
SELECT        742, 'HL Mountain Frame - Silver, 46', 'Silver',          3,        747, 'FR-M94S-46',          2 UNION ALL
SELECT        743, 'HL Mountain Frame - Black, 42', 'Black',          3,        739, 'FR-M94B-42',          2 UNION ALL
SELECT        744, 'HL Mountain Frame - Black, 44', 'Black',          3,        699, 'FR-M94B-44',          2 UNION ALL
SELECT        745, 'HL Mountain Frame - Black, 48', 'Black',          3,        699, 'FR-M94B-48',          2 UNION ALL
SELECT        746, 'HL Mountain Frame - Black, 46', 'Black',          3,        739, 'FR-M94B-46',          2 UNION ALL
SELECT        747, 'HL Mountain Frame - Black, 38', 'Black',          3,        739, 'FR-M94B-38',          2 UNION ALL
SELECT        748, 'HL Mountain Frame - Silver, 38', 'Silver',          3,        747, 'FR-M94S-38',          2 UNION ALL
SELECT        749, 'Road-150 Red, 62', 'Red',         15,       2171, 'BK-R93R-62',          1 UNION ALL
SELECT        750, 'Road-150 Red, 44', 'Red',         14,       2171, 'BK-R93R-44',          1 UNION ALL
SELECT        751, 'Road-150 Red, 48', 'Red',         14,       2171, 'BK-R93R-48',          1 UNION ALL
SELECT        752, 'Road-150 Red, 52', 'Red',         14,       2171, 'BK-R93R-52',          1 UNION ALL
SELECT        753, 'Road-150 Red, 56', 'Red',         15,       2171, 'BK-R93R-56',          1 UNION ALL
SELECT        754, 'Road-450 Red, 58', 'Red',         18,        885, 'BK-R68R-58',          1 UNION ALL
SELECT        755, 'Road-450 Red, 60', 'Red',         18,        885, 'BK-R68R-60',          1 UNION ALL
SELECT        756, 'Road-450 Red, 44', 'Red',         17,        885, 'BK-R68R-44',          1 UNION ALL
SELECT        757, 'Road-450 Red, 48', 'Red',         17,        885, 'BK-R68R-48',          1 UNION ALL
SELECT        758, 'Road-450 Red, 52', 'Red',         17,        885, 'BK-R68R-52',          1 UNION ALL
SELECT        759, 'Road-650 Red, 58', 'Red',         20,        487, 'BK-R50R-58',          1 UNION ALL
SELECT        760, 'Road-650 Red, 60', 'Red',         20,        487, 'BK-R50R-60',          1 UNION ALL
SELECT        761, 'Road-650 Red, 62', 'Red',         20,        487, 'BK-R50R-62',          1 UNION ALL
SELECT        762, 'Road-650 Red, 44', 'Red',         19,        487, 'BK-R50R-44',          1 UNION ALL
SELECT        763, 'Road-650 Red, 48', 'Red',         19,        487, 'BK-R50R-48',          1 UNION ALL
SELECT        764, 'Road-650 Red, 52', 'Red',         19,        487, 'BK-R50R-52',          1 UNION ALL
SELECT        765, 'Road-650 Black, 58', 'Black',         20,        487, 'BK-R50B-58',          1 UNION ALL
SELECT        766, 'Road-650 Black, 60', 'Black',         20,        487, 'BK-R50B-60',          1 UNION ALL
SELECT        767, 'Road-650 Black, 62', 'Black',         20,        487, 'BK-R50B-62',          1 UNION ALL
SELECT        768, 'Road-650 Black, 44', 'Black',         19,        487, 'BK-R50B-44',          1 UNION ALL
SELECT        769, 'Road-650 Black, 48', 'Black',         19,        487, 'BK-R50B-48',          1 UNION ALL
SELECT        770, 'Road-650 Black, 52', 'Black',         19,        487, 'BK-R50B-52',          1 UNION ALL
SELECT        771, 'Mountain-100 Silver, 38', 'Silver',         20,       1912, 'BK-M82S-38',          1 UNION ALL
SELECT        772, 'Mountain-100 Silver, 42', 'Silver',         21,       1912, 'BK-M82S-42',          1 UNION ALL
SELECT        773, 'Mountain-100 Silver, 44', 'Silver',         21,       1912, 'BK-M82S-44',          1 UNION ALL
SELECT        774, 'Mountain-100 Silver, 48', 'Silver',         21,       1912, 'BK-M82S-48',          1 UNION ALL
SELECT        775, 'Mountain-100 Black, 38', 'Black',         20,       1898, 'BK-M82B-38',          1 UNION ALL
SELECT        776, 'Mountain-100 Black, 42', 'Black',         21,       1898, 'BK-M82B-42',          1 UNION ALL
SELECT        777, 'Mountain-100 Black, 44', 'Black',         21,       1898, 'BK-M82B-44',          1 UNION ALL
SELECT        778, 'Mountain-100 Black, 48', 'Black',         21,       1898, 'BK-M82B-48',          1 UNION ALL
SELECT        779, 'Mountain-200 Silver, 38', 'Silver',         23,       1266, 'BK-M68S-38',          1 UNION ALL
SELECT        780, 'Mountain-200 Silver, 42', 'Silver',         24,       1266, 'BK-M68S-42',          1 UNION ALL
SELECT        781, 'Mountain-200 Silver, 46', 'Silver',         24,       1266, 'BK-M68S-46',          1 UNION ALL
SELECT        782, 'Mountain-200 Black, 38', 'Black',         23,       1252, 'BK-M68B-38',          1 UNION ALL
SELECT        783, 'Mountain-200 Black, 42', 'Black',         24,       1252, 'BK-M68B-42',          1 UNION ALL
SELECT        784, 'Mountain-200 Black, 46', 'Black',         24,       1252, 'BK-M68B-46',          1 UNION ALL
SELECT        785, 'Mountain-300 Black, 38', 'Black',         25,        598, 'BK-M47B-38',          1 UNION ALL
SELECT        786, 'Mountain-300 Black, 40', 'Black',         26,        598, 'BK-M47B-40',          1 UNION ALL
SELECT        787, 'Mountain-300 Black, 44', 'Black',         26,        598, 'BK-M47B-44',          1 UNION ALL
SELECT        788, 'Mountain-300 Black, 48', 'Black',         26,        598, 'BK-M47B-48',          1 UNION ALL
SELECT        789, 'Road-250 Red, 44', 'Red',         15,       1519, 'BK-R89R-44',          1 UNION ALL
SELECT        790, 'Road-250 Red, 48', 'Red',         15,       1519, 'BK-R89R-48',          1 UNION ALL
SELECT        791, 'Road-250 Red, 52', 'Red',         15,       1519, 'BK-R89R-52',          1 UNION ALL
SELECT        792, 'Road-250 Red, 58', 'Red',         16,       1555, 'BK-R89R-58',          1 UNION ALL
SELECT        793, 'Road-250 Black, 44', 'Black',         15,       1555, 'BK-R89B-44',          1 UNION ALL
SELECT        794, 'Road-250 Black, 48', 'Black',         15,       1555, 'BK-R89B-48',          1 UNION ALL
SELECT        795, 'Road-250 Black, 52', 'Black',         15,       1555, 'BK-R89B-52',          1 UNION ALL
SELECT        796, 'Road-250 Black, 58', 'Black',         16,       1555, 'BK-R89B-58',          1 UNION ALL
SELECT        797, 'Road-550-W Yellow, 38', 'Yellow',         17,        713, 'BK-R64Y-38',          1 UNION ALL
SELECT        798, 'Road-550-W Yellow, 40', 'Yellow',         18,        713, 'BK-R64Y-40',          1 UNION ALL
SELECT        799, 'Road-550-W Yellow, 42', 'Yellow',         18,        713, 'BK-R64Y-42',          1 UNION ALL
SELECT        800, 'Road-550-W Yellow, 44', 'Yellow',         18,        713, 'BK-R64Y-44',          1 UNION ALL
SELECT        801, 'Road-550-W Yellow, 48', 'Yellow',         19,        713, 'BK-R64Y-48',          1 UNION ALL
SELECT        802, 'LL Fork', 'NULL', NULL,         66, 'FK-1639',          2 UNION ALL
SELECT        803, 'ML Fork', 'NULL', NULL,         78, 'FK-5136',          2 UNION ALL
SELECT        804, 'HL Fork', 'NULL', NULL,        102, 'FK-9939',          2 UNION ALL
SELECT        805, 'LL Headset', 'NULL', NULL,         15, 'HS-0296',          2 UNION ALL
SELECT        806, 'ML Headset', 'NULL', NULL,         45, 'HS-2451',          2 UNION ALL
SELECT        807, 'HL Headset', 'NULL', NULL,         55, 'HS-3479',          2 UNION ALL
SELECT        808, 'LL Mountain Handlebars', 'NULL', NULL,         20, 'HB-M243',          2 UNION ALL
SELECT        809, 'ML Mountain Handlebars', 'NULL', NULL,         27, 'HB-M763',          2 UNION ALL
SELECT        810, 'HL Mountain Handlebars', 'NULL', NULL,         53, 'HB-M918',          2 UNION ALL
SELECT        811, 'LL Road Handlebars', 'NULL', NULL,         20, 'HB-R504',          2 UNION ALL
SELECT        812, 'ML Road Handlebars', 'NULL', NULL,         27, 'HB-R720',          2 UNION ALL
SELECT        813, 'HL Road Handlebars', 'NULL', NULL,         53, 'HB-R956',          2 UNION ALL
SELECT        814, 'ML Mountain Frame - Black, 38', 'Black',          3,        186, 'FR-M63B-38',          2 UNION ALL
SELECT        815, 'LL Mountain Front Wheel', 'Black', NULL,         27, 'FW-M423',          2 UNION ALL
SELECT        816, 'ML Mountain Front Wheel', 'Black', NULL,         93, 'FW-M762',          2 UNION ALL
SELECT        817, 'HL Mountain Front Wheel', 'Black', NULL,        133, 'FW-M928',          2 UNION ALL
SELECT        818, 'LL Road Front Wheel', 'Black',        900,         38, 'FW-R623',          2 UNION ALL
SELECT        819, 'ML Road Front Wheel', 'Black',        850,        110, 'FW-R762',          2 UNION ALL
SELECT        820, 'HL Road Front Wheel', 'Black',        650,        147, 'FW-R820',          2 UNION ALL
SELECT        821, 'Touring Front Wheel', 'Black', NULL,         97, 'FW-T905',          2 UNION ALL
SELECT        822, 'ML Road Frame-W - Yellow, 38', 'Yellow',          2,        361, 'FR-R72Y-38',          2 UNION ALL
SELECT        823, 'LL Mountain Rear Wheel', 'Black', NULL,         39, 'RW-M423',          2 UNION ALL
SELECT        824, 'ML Mountain Rear Wheel', 'Black', NULL,        105, 'RW-M762',          2 UNION ALL
SELECT        825, 'HL Mountain Rear Wheel', 'Black', NULL,        145, 'RW-M928',          2 UNION ALL
SELECT        826, 'LL Road Rear Wheel', 'Black',       1050,         50, 'RW-R623',          2 UNION ALL
SELECT        827, 'ML Road Rear Wheel', 'Black',       1000,        122, 'RW-R762',          2 UNION ALL
SELECT        828, 'HL Road Rear Wheel', 'Black',        890,        159, 'RW-R820',          2 UNION ALL
SELECT        829, 'Touring Rear Wheel', 'Black', NULL,        109, 'RW-T905',          2 UNION ALL
SELECT        830, 'ML Mountain Frame - Black, 40', 'Black',          3,        186, 'FR-M63B-40',          2 UNION ALL
SELECT        831, 'ML Mountain Frame - Black, 44', 'Black',          3,        186, 'FR-M63B-44',          2 UNION ALL
SELECT        832, 'ML Mountain Frame - Black, 48', 'Black',          3,        186, 'FR-M63B-48',          2 UNION ALL
SELECT        833, 'ML Road Frame-W - Yellow, 40', 'Yellow',          2,        361, 'FR-R72Y-40',          2 UNION ALL
SELECT        834, 'ML Road Frame-W - Yellow, 42', 'Yellow',          2,        361, 'FR-R72Y-42',          2 UNION ALL
SELECT        835, 'ML Road Frame-W - Yellow, 44', 'Yellow',          2,        361, 'FR-R72Y-44',          2 UNION ALL
SELECT        836, 'ML Road Frame-W - Yellow, 48', 'Yellow',          2,        361, 'FR-R72Y-48',          2 UNION ALL
SELECT        837, 'HL Road Frame - Black, 62', 'Black',          2,        869, 'FR-R92B-62',          2 UNION ALL
SELECT        838, 'HL Road Frame - Black, 44', 'Black',          2,        869, 'FR-R92B-44',          2 UNION ALL
SELECT        839, 'HL Road Frame - Black, 48', 'Black',          2,        869, 'FR-R92B-48',          2 UNION ALL
SELECT        840, 'HL Road Frame - Black, 52', 'Black',          2,        869, 'FR-R92B-52',          2 UNION ALL
SELECT        841, 'Men''s Sports Shorts, S', 'Black', NULL,         25, 'SH-M897-S',          3 UNION ALL
SELECT        842, 'Touring-Panniers, Large', 'Grey', NULL,         52, 'PA-T100',          4 UNION ALL
SELECT        843, 'Cable Lock', 'NULL', NULL,         10, 'LO-C100',          4 UNION ALL
SELECT        844, 'Minipump', 'NULL', NULL,          8, 'PU-0452',          4 UNION ALL
SELECT        845, 'Mountain Pump', 'NULL', NULL,         10, 'PU-M044',          4 UNION ALL
SELECT        846, 'Taillights - Battery-Powered', 'NULL', NULL,          6, 'LT-T990',          4 UNION ALL
SELECT        847, 'Headlights - Dual-Beam', 'NULL', NULL,         14, 'LT-H902',          4 UNION ALL
SELECT        848, 'Headlights - Weatherproof', 'NULL', NULL,         19, 'LT-H903',          4 UNION ALL
SELECT        849, 'Men''s Sports Shorts, M', 'Black', NULL,         25, 'SH-M897-M',          3 UNION ALL
SELECT        850, 'Men''s Sports Shorts, L', 'Black', NULL,         25, 'SH-M897-L',          3 UNION ALL
SELECT        851, 'Men''s Sports Shorts, XL', 'Black', NULL,         25, 'SH-M897-X',          3 UNION ALL
SELECT        852, 'Women''s Tights, S', 'Black', NULL,         31, 'TG-W091-S',          3 UNION ALL
SELECT        853, 'Women''s Tights, M', 'Black', NULL,         31, 'TG-W091-M',          3 UNION ALL
SELECT        854, 'Women''s Tights, L', 'Black', NULL,         31, 'TG-W091-L',          3 UNION ALL
SELECT        855, 'Men''s Bib-Shorts, S', 'Multi', NULL,         37, 'SB-M891-S',          3 UNION ALL
SELECT        856, 'Men''s Bib-Shorts, M', 'Multi', NULL,         37, 'SB-M891-M',          3 UNION ALL
SELECT        857, 'Men''s Bib-Shorts, L', 'Multi', NULL,         37, 'SB-M891-L',          3 UNION ALL
SELECT        858, 'Half-Finger Gloves, S', 'Black', NULL,          9, 'GL-H102-S',          3 UNION ALL
SELECT        859, 'Half-Finger Gloves, M', 'Black', NULL,          9, 'GL-H102-M',          3 UNION ALL
SELECT        860, 'Half-Finger Gloves, L', 'Black', NULL,          9, 'GL-H102-L',          3 UNION ALL
SELECT        861, 'Full-Finger Gloves, S', 'Black', NULL,         16, 'GL-F110-S',          3 UNION ALL
SELECT        862, 'Full-Finger Gloves, M', 'Black', NULL,         16, 'GL-F110-M',          3 UNION ALL
SELECT        863, 'Full-Finger Gloves, L', 'Black', NULL,         16, 'GL-F110-L',          3 UNION ALL
SELECT        864, 'Classic Vest, S', 'Blue', NULL,         24, 'VE-C304-S',          3 UNION ALL
SELECT        865, 'Classic Vest, M', 'Blue', NULL,         24, 'VE-C304-M',          3 UNION ALL
SELECT        866, 'Classic Vest, L', 'Blue', NULL,         24, 'VE-C304-L',          3 UNION ALL
SELECT        867, 'Women''s Mountain Shorts, S', 'Black', NULL,         26, 'SH-W890-S',          3 UNION ALL
SELECT        868, 'Women''s Mountain Shorts, M', 'Black', NULL,         26, 'SH-W890-M',          3 UNION ALL
SELECT        869, 'Women''s Mountain Shorts, L', 'Black', NULL,         26, 'SH-W890-L',          3 UNION ALL
SELECT        870, 'Water Bottle - 30 oz.', 'NULL', NULL,          2, 'WB-H098',          4 UNION ALL
SELECT        871, 'Mountain Bottle Cage', 'NULL', NULL,          4, 'BC-M005',          4 UNION ALL
SELECT        872, 'Road Bottle Cage', 'NULL', NULL,          3, 'BC-R205',          4 UNION ALL
SELECT        873, 'Patch Kit/8 Patches', 'NULL', NULL,          1, 'PK-7098',          4 UNION ALL
SELECT        874, 'Racing Socks, M', 'White', NULL,          3, 'SO-R809-M',          3 UNION ALL
SELECT        875, 'Racing Socks, L', 'White', NULL,          3, 'SO-R809-L',          3 UNION ALL
SELECT        876, 'Hitch Rack - 4-Bike', 'NULL', NULL,         45, 'RA-H123',          4 UNION ALL
SELECT        877, 'Bike Wash - Dissolver', 'NULL', NULL,          3, 'CL-9009',          4 UNION ALL
SELECT        878, 'Fender Set - Mountain', 'NULL', NULL,          8, 'FE-6654',          4 UNION ALL
SELECT        879, 'All-Purpose Bike Stand', 'NULL', NULL,         59, 'ST-1401',          4 UNION ALL
SELECT        880, 'Hydration Pack - 70 oz.', 'Silver', NULL,         21, 'HY-1023-70',          4 UNION ALL
SELECT        881, 'Short-Sleeve Classic Jersey, S', 'Yellow', NULL,         42, 'SJ-0194-S',          3 UNION ALL
SELECT        882, 'Short-Sleeve Classic Jersey, M', 'Yellow', NULL,         42, 'SJ-0194-M',          3 UNION ALL
SELECT        883, 'Short-Sleeve Classic Jersey, L', 'Yellow', NULL,         42, 'SJ-0194-L',          3 UNION ALL
SELECT        884, 'Short-Sleeve Classic Jersey, XL', 'Yellow', NULL,         42, 'SJ-0194-X',          3 UNION ALL
SELECT        885, 'HL Touring Frame - Yellow, 60', 'Yellow',          3,        602, 'FR-T98Y-60',          2 UNION ALL
SELECT        886, 'LL Touring Frame - Yellow, 62', 'Yellow',          3,        200, 'FR-T67Y-62',          2 UNION ALL
SELECT        887, 'HL Touring Frame - Yellow, 46', 'Yellow',          3,        602, 'FR-T98Y-46',          2 UNION ALL
SELECT        888, 'HL Touring Frame - Yellow, 50', 'Yellow',          3,        602, 'FR-T98Y-50',          2 UNION ALL
SELECT        889, 'HL Touring Frame - Yellow, 54', 'Yellow',          3,        602, 'FR-T98Y-54',          2 UNION ALL
SELECT        890, 'HL Touring Frame - Blue, 46', 'Blue',          3,        602, 'FR-T98U-46',          2 UNION ALL
SELECT        891, 'HL Touring Frame - Blue, 50', 'Blue',          3,        602, 'FR-T98U-50',          2 UNION ALL
SELECT        892, 'HL Touring Frame - Blue, 54', 'Blue',          3,        602, 'FR-T98U-54',          2 UNION ALL
SELECT        893, 'HL Touring Frame - Blue, 60', 'Blue',          3,        602, 'FR-T98U-60',          2 UNION ALL
SELECT        894, 'Rear Derailleur', 'Silver',        215,         54, 'RD-2308',          2 UNION ALL
SELECT        895, 'LL Touring Frame - Blue, 50', 'Blue',          3,        200, 'FR-T67U-50',          2 UNION ALL
SELECT        896, 'LL Touring Frame - Blue, 54', 'Blue',          3,        200, 'FR-T67U-54',          2 UNION ALL
SELECT        897, 'LL Touring Frame - Blue, 58', 'Blue',          3,        200, 'FR-T67U-58',          2 UNION ALL
SELECT        898, 'LL Touring Frame - Blue, 62', 'Blue',          3,        200, 'FR-T67U-62',          2 UNION ALL
SELECT        899, 'LL Touring Frame - Yellow, 44', 'Yellow',          3,        200, 'FR-T67Y-44',          2 UNION ALL
SELECT        900, 'LL Touring Frame - Yellow, 50', 'Yellow',          3,        200, 'FR-T67Y-50',          2 UNION ALL
SELECT        901, 'LL Touring Frame - Yellow, 54', 'Yellow',          3,        200, 'FR-T67Y-54',          2 UNION ALL
SELECT        902, 'LL Touring Frame - Yellow, 58', 'Yellow',          3,        200, 'FR-T67Y-58',          2 UNION ALL
SELECT        903, 'LL Touring Frame - Blue, 44', 'Blue',          3,        200, 'FR-T67U-44',          2 UNION ALL
SELECT        904, 'ML Mountain Frame-W - Silver, 40', 'Silver',          3,        199, 'FR-M63S-40',          2 UNION ALL
SELECT        905, 'ML Mountain Frame-W - Silver, 42', 'Silver',          3,        199, 'FR-M63S-42',          2 UNION ALL
SELECT        906, 'ML Mountain Frame-W - Silver, 46', 'Silver',          3,        199, 'FR-M63S-46',          2 UNION ALL
SELECT        907, 'Rear Brakes', 'Silver',        317,         47, 'RB-9231',          2 UNION ALL
SELECT        908, 'LL Mountain Seat/Saddle', 'NULL', NULL,         12, 'SE-M236',          2 UNION ALL
SELECT        909, 'ML Mountain Seat/Saddle', 'NULL', NULL,         17, 'SE-M798',          2 UNION ALL
SELECT        910, 'HL Mountain Seat/Saddle', 'NULL', NULL,         23, 'SE-M940',          2 UNION ALL
SELECT        911, 'LL Road Seat/Saddle', 'NULL', NULL,         12, 'SE-R581',          2 UNION ALL
SELECT        912, 'ML Road Seat/Saddle', 'NULL', NULL,         17, 'SE-R908',          2 UNION ALL
SELECT        913, 'HL Road Seat/Saddle', 'NULL', NULL,         23, 'SE-R995',          2 UNION ALL
SELECT        914, 'LL Touring Seat/Saddle', 'NULL', NULL,         12, 'SE-T312',          2 UNION ALL
SELECT        915, 'ML Touring Seat/Saddle', 'NULL', NULL,         17, 'SE-T762',          2 UNION ALL
SELECT        916, 'HL Touring Seat/Saddle', 'NULL', NULL,         23, 'SE-T924',          2 UNION ALL
SELECT        917, 'LL Mountain Frame - Silver, 42', 'Silver',          3,        145, 'FR-M21S-42',          2 UNION ALL
SELECT        918, 'LL Mountain Frame - Silver, 44', 'Silver',          3,        145, 'FR-M21S-44',          2 UNION ALL
SELECT        919, 'LL Mountain Frame - Silver, 48', 'Silver',          3,        145, 'FR-M21S-48',          2 UNION ALL
SELECT        920, 'LL Mountain Frame - Silver, 52', 'Silver',          3,        145, 'FR-M21S-52',          2 UNION ALL
SELECT        921, 'Mountain Tire Tube', 'NULL', NULL,          2, 'TT-M928',          4 UNION ALL
SELECT        922, 'Road Tire Tube', 'NULL', NULL,          1, 'TT-R982',          4 UNION ALL
SELECT        923, 'Touring Tire Tube', 'NULL', NULL,          2, 'TT-T092',          4 UNION ALL
SELECT        924, 'LL Mountain Frame - Black, 42', 'Black',          3,        137, 'FR-M21B-42',          2 UNION ALL
SELECT        925, 'LL Mountain Frame - Black, 44', 'Black',          3,        137, 'FR-M21B-44',          2 UNION ALL
SELECT        926, 'LL Mountain Frame - Black, 48', 'Black',          3,        137, 'FR-M21B-48',          2 UNION ALL
SELECT        927, 'LL Mountain Frame - Black, 52', 'Black',          3,        137, 'FR-M21B-52',          2 UNION ALL
SELECT        928, 'LL Mountain Tire', 'NULL', NULL,          9, 'TI-M267',          4 UNION ALL
SELECT        929, 'ML Mountain Tire', 'NULL', NULL,         11, 'TI-M602',          4 UNION ALL
SELECT        930, 'HL Mountain Tire', 'NULL', NULL,         13, 'TI-M823',          4 UNION ALL
SELECT        931, 'LL Road Tire', 'NULL', NULL,          8, 'TI-R092',          4 UNION ALL
SELECT        932, 'ML Road Tire', 'NULL', NULL,          9, 'TI-R628',          4 UNION ALL
SELECT        933, 'HL Road Tire', 'NULL', NULL,         12, 'TI-R982',          4 UNION ALL
SELECT        934, 'Touring Tire', 'NULL', NULL,         11, 'TI-T723',          4 UNION ALL
SELECT        935, 'LL Mountain Pedal', 'Silver/Black',        218,         18, 'PD-M282',          2 UNION ALL
SELECT        936, 'ML Mountain Pedal', 'Silver/Black',        215,         28, 'PD-M340',          2 UNION ALL
SELECT        937, 'HL Mountain Pedal', 'Silver/Black',        185,         36, 'PD-M562',          2 UNION ALL
SELECT        938, 'LL Road Pedal', 'Silver/Black',        189,         18, 'PD-R347',          2 UNION ALL
SELECT        939, 'ML Road Pedal', 'Silver/Black',        168,         28, 'PD-R563',          2 UNION ALL
SELECT        940, 'HL Road Pedal', 'Silver/Black',        149,         36, 'PD-R853',          2 UNION ALL
SELECT        941, 'Touring Pedal', 'Silver/Black', NULL,         36, 'PD-T852',          2 UNION ALL
SELECT        942, 'ML Mountain Frame-W - Silver, 38', 'Silver',          3,        199, 'FR-M63S-38',          2 UNION ALL
SELECT        943, 'LL Mountain Frame - Black, 40', 'Black',          3,        137, 'FR-M21B-40',          2 UNION ALL
SELECT        944, 'LL Mountain Frame - Silver, 40', 'Silver',          3,        145, 'FR-M21S-40',          2 UNION ALL
SELECT        945, 'Front Derailleur', 'Silver',         88,         41, 'FD-2342',          2 UNION ALL
SELECT        946, 'LL Touring Handlebars', 'NULL', NULL,         20, 'HB-T721',          2 UNION ALL
SELECT        947, 'HL Touring Handlebars', 'NULL', NULL,         41, 'HB-T928',          2 UNION ALL
SELECT        948, 'Front Brakes', 'Silver',        317,         47, 'FB-9873',          2 UNION ALL
SELECT        949, 'LL Crankset', 'Black',        600,         78, 'CS-4759',          2 UNION ALL
SELECT        950, 'ML Crankset', 'Black',        635,        114, 'CS-6583',          2 UNION ALL
SELECT        951, 'HL Crankset', 'Black',        575,        180, 'CS-9183',          2 UNION ALL
SELECT        952, 'Chain', 'Silver', NULL,          9, 'CH-0234',          2 UNION ALL
SELECT        953, 'Touring-2000 Blue, 60', 'Blue',         28,        755, 'BK-T44U-60',          1 UNION ALL
SELECT        954, 'Touring-1000 Yellow, 46', 'Yellow',         25,       1482, 'BK-T79Y-46',          1 UNION ALL
SELECT        955, 'Touring-1000 Yellow, 50', 'Yellow',         25,       1482, 'BK-T79Y-50',          1 UNION ALL
SELECT        956, 'Touring-1000 Yellow, 54', 'Yellow',         26,       1482, 'BK-T79Y-54',          1 UNION ALL
SELECT        957, 'Touring-1000 Yellow, 60', 'Yellow',         26,       1482, 'BK-T79Y-60',          1 UNION ALL
SELECT        958, 'Touring-3000 Blue, 54', 'Blue',         30,        461, 'BK-T18U-54',          1 UNION ALL
SELECT        959, 'Touring-3000 Blue, 58', 'Blue',         30,        461, 'BK-T18U-58',          1 UNION ALL
SELECT        960, 'Touring-3000 Blue, 62', 'Blue',         30,        461, 'BK-T18U-62',          1 UNION ALL
SELECT        961, 'Touring-3000 Yellow, 44', 'Yellow',         29,        461, 'BK-T18Y-44',          1 UNION ALL
SELECT        962, 'Touring-3000 Yellow, 50', 'Yellow',         29,        461, 'BK-T18Y-50',          1 UNION ALL
SELECT        963, 'Touring-3000 Yellow, 54', 'Yellow',         29,        461, 'BK-T18Y-54',          1 UNION ALL
SELECT        964, 'Touring-3000 Yellow, 58', 'Yellow',         30,        461, 'BK-T18Y-58',          1 UNION ALL
SELECT        965, 'Touring-3000 Yellow, 62', 'Yellow',         30,        461, 'BK-T18Y-62',          1 UNION ALL
SELECT        966, 'Touring-1000 Blue, 46', 'Blue',         25,       1482, 'BK-T79U-46',          1 UNION ALL
SELECT        967, 'Touring-1000 Blue, 50', 'Blue',         25,       1482, 'BK-T79U-50',          1 UNION ALL
SELECT        968, 'Touring-1000 Blue, 54', 'Blue',         26,       1482, 'BK-T79U-54',          1 UNION ALL
SELECT        969, 'Touring-1000 Blue, 60', 'Blue',         26,       1482, 'BK-T79U-60',          1 UNION ALL
SELECT        970, 'Touring-2000 Blue, 46', 'Blue',         27,        755, 'BK-T44U-46',          1 UNION ALL
SELECT        971, 'Touring-2000 Blue, 50', 'Blue',         27,        755, 'BK-T44U-50',          1 UNION ALL
SELECT        972, 'Touring-2000 Blue, 54', 'Blue',         28,        755, 'BK-T44U-54',          1 UNION ALL
SELECT        973, 'Road-350-W Yellow, 40', 'Yellow',         15,       1083, 'BK-R79Y-40',          1 UNION ALL
SELECT        974, 'Road-350-W Yellow, 42', 'Yellow',         16,       1083, 'BK-R79Y-42',          1 UNION ALL
SELECT        975, 'Road-350-W Yellow, 44', 'Yellow',         16,       1083, 'BK-R79Y-44',          1 UNION ALL
SELECT        976, 'Road-350-W Yellow, 48', 'Yellow',         16,       1083, 'BK-R79Y-48',          1 UNION ALL
SELECT        977, 'Road-750 Black, 58', 'Black',         21,        344, 'BK-R19B-58',          1 UNION ALL
SELECT        978, 'Touring-3000 Blue, 44', 'Blue',         29,        461, 'BK-T18U-44',          1 UNION ALL
SELECT        979, 'Touring-3000 Blue, 50', 'Blue',         29,        461, 'BK-T18U-50',          1 UNION ALL
SELECT        980, 'Mountain-400-W Silver, 38', 'Silver',         26,        420, 'BK-M38S-38',          1 UNION ALL
SELECT        981, 'Mountain-400-W Silver, 40', 'Silver',         27,        420, 'BK-M38S-40',          1 UNION ALL
SELECT        982, 'Mountain-400-W Silver, 42', 'Silver',         27,        420, 'BK-M38S-42',          1 UNION ALL
SELECT        983, 'Mountain-400-W Silver, 46', 'Silver',         27,        420, 'BK-M38S-46',          1 UNION ALL
SELECT        984, 'Mountain-500 Silver, 40', 'Silver',         27,        308, 'BK-M18S-40',          1 UNION ALL
SELECT        985, 'Mountain-500 Silver, 42', 'Silver',         28,        308, 'BK-M18S-42',          1 UNION ALL
SELECT        986, 'Mountain-500 Silver, 44', 'Silver',         28,        308, 'BK-M18S-44',          1 UNION ALL
SELECT        987, 'Mountain-500 Silver, 48', 'Silver',         28,        308, 'BK-M18S-48',          1 UNION ALL
SELECT        988, 'Mountain-500 Silver, 52', 'Silver',         29,        308, 'BK-M18S-52',          1 UNION ALL
SELECT        989, 'Mountain-500 Black, 40', 'Black',         27,        295, 'BK-M18B-40',          1 UNION ALL
SELECT        990, 'Mountain-500 Black, 42', 'Black',         28,        295, 'BK-M18B-42',          1 UNION ALL
SELECT        991, 'Mountain-500 Black, 44', 'Black',         28,        295, 'BK-M18B-44',          1 UNION ALL
SELECT        992, 'Mountain-500 Black, 48', 'Black',         28,        295, 'BK-M18B-48',          1 UNION ALL
SELECT        993, 'Mountain-500 Black, 52', 'Black',         29,        295, 'BK-M18B-52',          1 UNION ALL
SELECT        994, 'LL Bottom Bracket', 'NULL',        223,         24, 'BB-7421',          2 UNION ALL
SELECT        995, 'ML Bottom Bracket', 'NULL',        168,         45, 'BB-8107',          2 UNION ALL
SELECT        996, 'HL Bottom Bracket', 'NULL',        170,         54, 'BB-9108',          2 UNION ALL
SELECT        997, 'Road-750 Black, 44', 'Black',         20,        344, 'BK-R19B-44',          1 UNION ALL
SELECT        998, 'Road-750 Black, 48', 'Black',         20,        344, 'BK-R19B-48',          1 UNION ALL
SELECT        999, 'Road-750 Black, 52', 'Black',         20,        344, 'BK-R19B-52',          1 
)
MERGE INTO dbo.Products AS tgt
USING cProducts AS src
ON tgt.ProductID = src.ProductID

WHEN MATCHED THEN UPDATE
SET ProductName = src.ProductName, Colour = src.Colour, WeightKG = src.WeightKG, Cost = src.Cost, ProductCode = src.ProductCode, ProductCategoryID = src.ProductCategoryID

WHEN NOT MATCHED THEN INSERT(ProductID, ProductName, Colour, WeightKG, Cost, ProductCode, ProductCategoryID)
VALUES (src.ProductID, src.ProductName, src.Colour, src.WeightKG, src.Cost, src.ProductCode, src.ProductCategoryID)
;
GO
SET IDENTITY_INSERT dbo.Products OFF;
GO
SET IDENTITY_INSERT dbo.[Address] ON;
GO

WITH cData(StreetNumber, AddressLine1, City, Region, PostCode, Country) AS (

	SELECT 65, '568-1433 Auctor St.', 'Chastre-Villeroux-Blanmont', 1, '931', 'New Zealand' UNION ALL
	SELECT 39, 'P.O. Box 463, 9290 Ipsum Road', 'Mansfield', 3, '7871', 'New Zealand' UNION ALL
	SELECT 115, '473-2540 Pede Av.', 'Belgaum', 4, '2427', 'New Zealand' UNION ALL
	SELECT 122, '3431 Magna. Av.', 'Raigarh', 3, '918', 'New Zealand' UNION ALL
	SELECT 12, 'Ap #628-1771 Neque Av.', 'College', 2, '9164', 'New Zealand' UNION ALL
	SELECT 139, 'P.O. Box 273, 4246 Nunc Road', 'Neuruppin', 4, '1056', 'New Zealand' UNION ALL
	SELECT 188, 'Ap #839-6396 Tellus, Ave', 'Woodstock', 3, '7771', 'New Zealand' UNION ALL
	SELECT 133, 'P.O. Box 735, 8633 Donec Street', 'Castiglione Messer Raimondo', 3, '8920', 'New Zealand' UNION ALL
	SELECT 160, 'Ap #986-1889 Enim. Avenue', 'San Maurizio Canavese', 1, '8370', 'New Zealand' UNION ALL
	SELECT 91, 'P.O. Box 632, 3268 Euismod Ave', 'Wilmington', 1, '3453', 'New Zealand' UNION ALL
	SELECT 113, '357 Quis Avenue', 'Mattersburg', 4, '4840', 'New Zealand' UNION ALL
	SELECT 200, 'P.O. Box 445, 3249 Egestas Ave', 'Biesme-sous-Thuin', 2, '1389', 'New Zealand' UNION ALL
	SELECT 51, 'Ap #261-1346 Cum Ave', 'Bhavnagar', 1, '2167', 'New Zealand' UNION ALL
	SELECT 96, '7421 Gravida. Rd.', 'Bear', 3, '1500', 'New Zealand' UNION ALL
	SELECT 94, '705-6861 Egestas Rd.', 'Anchorage', 3, '2082', 'New Zealand' UNION ALL
	SELECT 3, '336-6405 A, Rd.', 'Sint-Genesius-Rode', 3, '532', 'New Zealand' UNION ALL
	SELECT 197, 'P.O. Box 346, 177 Ligula. St.', 'Parramatta', 4, '1816', 'New Zealand' UNION ALL
	SELECT 30, '6994 Proin Avenue', 'Warrnambool', 3, '5677', 'New Zealand' UNION ALL
	SELECT 47, '662-8681 Pede Rd.', 'Coquitlam', 3, '3819', 'New Zealand' UNION ALL
	SELECT 85, 'P.O. Box 234, 6840 Nunc St.', 'Court-Saint-Etienne', 3, '8926', 'New Zealand' UNION ALL
	SELECT 153, 'P.O. Box 167, 9151 Nisl Av.', 'Nanton', 4, '5789', 'New Zealand' UNION ALL
	SELECT 42, 'P.O. Box 534, 3707 Eu, Ave', 'Mosciano Sant''Angelo', 1, '6284', 'New Zealand' UNION ALL
	SELECT 180, '1112 Amet Rd.', 'San Jose', 4, '5319', 'New Zealand' UNION ALL
	SELECT 76, '2789 Consequat Avenue', 'Sprimont', 4, '80', 'New Zealand' UNION ALL
	SELECT 165, '390-2518 Vitae Road', 'Wazirabad', 4, '2194', 'New Zealand' UNION ALL
	SELECT 105, 'Ap #244-3817 Libero. Ave', 'Longueville', 3, '9742', 'New Zealand' UNION ALL
	SELECT 115, '1211 Placerat St.', 'Indore', 4, '4795', 'New Zealand' UNION ALL
	SELECT 155, 'Ap #991-906 In Rd.', 'Carovilli', 1, '149', 'New Zealand' UNION ALL
	SELECT 131, '5307 Sagittis Road', 'Deschambault', 2, '7490', 'New Zealand' UNION ALL
	SELECT 36, '294-8515 Vitae, Rd.', 'Piagge', 2, '1406', 'New Zealand' UNION ALL
	SELECT 13, '6807 Cursus Ave', 'Stranraer', 1, '1961', 'New Zealand' UNION ALL
	SELECT 163, 'P.O. Box 682, 7852 Posuere Rd.', 'Elversele', 4, '3711', 'New Zealand' UNION ALL
	SELECT 198, 'P.O. Box 820, 8273 Vehicula Street', 'Burdinne', 1, '3304', 'New Zealand' UNION ALL
	SELECT 162, 'P.O. Box 137, 9280 Urna Rd.', 'Des Moines', 2, '4493', 'New Zealand' UNION ALL
	SELECT 178, 'P.O. Box 245, 8734 Lorem, Avenue', 'Hamme-Mille', 4, '9111', 'New Zealand' UNION ALL
	SELECT 97, 'P.O. Box 291, 540 Amet Av.', 'Konstanz', 4, '1021', 'New Zealand' UNION ALL
	SELECT 106, 'P.O. Box 700, 9049 Sem St.', 'Mignanego', 3, '2949', 'New Zealand' UNION ALL
	SELECT 175, '876 Duis Street', 'Sint-Niklaas', 3, '8952', 'New Zealand' UNION ALL
	SELECT 48, 'P.O. Box 448, 9974 Urna Avenue', 'Kearny', 4, '3694', 'New Zealand' UNION ALL
	SELECT 106, 'Ap #487-3225 Sed Avenue', 'Fort Simpson', 2, '9097', 'New Zealand' UNION ALL
	SELECT 18, 'P.O. Box 136, 9126 Duis Rd.', 'Nijmegen', 4, '5272', 'New Zealand' UNION ALL
	SELECT 24, 'Ap #728-9076 Non Rd.', 'Bothey', 3, '6933', 'New Zealand' UNION ALL
	SELECT 120, '5726 Mollis. Av.', 'Ways', 2, '6886', 'New Zealand' UNION ALL
	SELECT 91, '180-1190 Eu Avenue', 'College', 4, '7705', 'New Zealand' UNION ALL
	SELECT 141, '120-2592 Nunc St.', 'Essex', 4, '1026', 'New Zealand' UNION ALL
	SELECT 191, 'Ap #416-7688 Risus. Av.', 'Massa e Cozzile', 1, '4552', 'New Zealand' UNION ALL
	SELECT 18, '4520 Semper Av.', 'Presteigne', 2, '7223', 'New Zealand' UNION ALL
	SELECT 130, '7869 Ornare, Av.', 'Galbiate', 2, '8521', 'New Zealand' UNION ALL
	SELECT 11, '187-3581 Dui Street', 'Chelsea', 4, '6765', 'New Zealand' UNION ALL
	SELECT 190, '432-2233 Faucibus Street', 'Linz', 4, '8426', 'New Zealand' UNION ALL
	SELECT 130, '672-4030 Donec Road', 'Landeck', 2, '9108', 'New Zealand' UNION ALL
	SELECT 9, 'Ap #724-1728 Eu, Avenue', 'Bomal', 1, '2110', 'New Zealand' UNION ALL
	SELECT 29, '393-9059 Eleifend. St.', 'Lampertheim', 2, '7656', 'New Zealand' UNION ALL
	SELECT 137, 'P.O. Box 419, 408 Dolor, Avenue', 'Penrith', 2, '5180', 'New Zealand' UNION ALL
	SELECT 162, '9071 Dui, Rd.', 'Perth', 4, '7227', 'New Zealand' UNION ALL
	SELECT 131, 'Ap #172-2994 Elit. Rd.', 'Nobressart', 4, '4162', 'New Zealand' UNION ALL
	SELECT 46, 'Ap #217-2577 Erat St.', 'Anchorage', 1, '1659', 'New Zealand' UNION ALL
	SELECT 111, 'P.O. Box 303, 4007 Phasellus Road', 'Scunthorpe', 1, '3521', 'New Zealand' UNION ALL
	SELECT 46, 'P.O. Box 498, 1615 Odio Rd.', 'Pincher Creek', 3, '8072', 'New Zealand' UNION ALL
	SELECT 93, 'Ap #347-6006 A Street', 'Baltimore', 3, '9357', 'New Zealand' UNION ALL
	SELECT 149, 'P.O. Box 316, 9843 Consequat Avenue', 'Hugli-Chinsurah', 4, '20', 'New Zealand' UNION ALL
	SELECT 180, '937-1556 Nibh Rd.', 'Amersfoort', 3, '1384', 'New Zealand' UNION ALL
	SELECT 188, 'P.O. Box 729, 4560 Vestibulum St.', 'Comeglians', 1, '5864', 'New Zealand' UNION ALL
	SELECT 101, '739-4873 Sed, Av.', 'Liernu', 4, '3782', 'New Zealand' UNION ALL
	SELECT 147, 'P.O. Box 943, 3531 Sociis Rd.', 'l''Ecluse', 4, '2700', 'New Zealand' UNION ALL
	SELECT 67, 'P.O. Box 927, 2914 Cum Road', 'Bhavnagar', 3, '2276', 'New Zealand' UNION ALL
	SELECT 55, 'P.O. Box 877, 2344 Donec St.', 'Warspite', 1, '8188', 'New Zealand' UNION ALL
	SELECT 193, '971-546 Cras Street', 'Wimbledon', 1, '8010', 'New Zealand' UNION ALL
	SELECT 42, '7647 Velit. Avenue', 'Chiauci', 3, '5441', 'New Zealand' UNION ALL
	SELECT 103, 'P.O. Box 967, 7164 Consequat Rd.', 'Newbury', 3, '6966', 'New Zealand' UNION ALL
	SELECT 158, 'P.O. Box 867, 3378 Urna. Rd.', 'Fumal', 1, '4376', 'New Zealand' UNION ALL
	SELECT 106, '7790 Ipsum. Avenue', 'Chandannagar', 4, '9032', 'New Zealand' UNION ALL
	SELECT 50, 'P.O. Box 982, 7755 Eu Ave', 'Harlingen', 2, '5142', 'New Zealand' UNION ALL
	SELECT 64, '392-7573 Iaculis Rd.', 'Whitby', 2, '9360', 'New Zealand' UNION ALL
	SELECT 47, '4188 Justo Road', 'Eisenh�ttenstadt', 1, '3483', 'New Zealand' UNION ALL
	SELECT 177, '6704 Enim. Ave', 'Loy', 3, '3788', 'New Zealand' UNION ALL
	SELECT 164, '3236 Dapibus Avenue', 'Guntur', 3, '4565', 'New Zealand' UNION ALL
	SELECT 1, '3475 Bibendum. Avenue', 'Jaén', 4, '7795', 'New Zealand' UNION ALL
	SELECT 13, '3185 Erat Av.', 'La Rochelle', 3, '7444', 'New Zealand' UNION ALL
	SELECT 127, 'Ap #585-4895 Nisl. Rd.', 'Guelph', 1, '6096', 'New Zealand' UNION ALL
	SELECT 85, '161-3237 Malesuada Street', 'Badalona', 2, '6439', 'New Zealand' UNION ALL
	SELECT 47, '5923 Enim Street', 'Tuscaloosa', 3, '7204', 'New Zealand' UNION ALL
	SELECT 135, 'Ap #465-1493 Velit Av.', 'Guna', 2, '4777', 'New Zealand' UNION ALL
	SELECT 97, '450-4457 Semper St.', 'Ledbury', 4, '8942', 'New Zealand' UNION ALL
	SELECT 18, '5369 Lacus. Avenue', 'Tredegar', 2, '6541', 'New Zealand' UNION ALL
	SELECT 113, '323-8659 Tortor. St.', 'Salice Salentino', 3, '6204', 'New Zealand' UNION ALL
	SELECT 172, '7171 Imperdiet Rd.', 'Bruderheim', 2, '4802', 'New Zealand' UNION ALL
	SELECT 127, '5359 Dolor. Road', 'Pelago', 3, '5761', 'New Zealand' UNION ALL
	SELECT 66, 'P.O. Box 934, 3159 Sem. St.', 'Rexton', 1, '5460', 'New Zealand' UNION ALL
	SELECT 42, '909 Fringilla Rd.', 'Naperville', 4, '9963', 'New Zealand' UNION ALL
	SELECT 180, '5456 Vehicula. Rd.', 'Caprauna', 1, '3634', 'New Zealand' UNION ALL
	SELECT 140, 'Ap #676-5620 Est, Rd.', 'Bayreuth', 3, '5549', 'New Zealand' UNION ALL
	SELECT 117, 'P.O. Box 755, 1959 Sit Rd.', 'Holman', 4, '383', 'New Zealand' UNION ALL
	SELECT 51, '486-7316 Nec, St.', 'San Maurizio Canavese', 2, '5135', 'New Zealand' UNION ALL
	SELECT 91, 'Ap #186-5451 Eu, Av.', 'Broechem', 1, '3293', 'New Zealand' UNION ALL
	SELECT 120, 'Ap #535-6992 Tortor Ave', 'Campomorone', 1, '9972', 'New Zealand' UNION ALL
	SELECT 19, 'P.O. Box 426, 1558 Auctor Rd.', 'Pittsburgh', 3, '8217', 'New Zealand' UNION ALL
	SELECT 153, 'Ap #959-5983 In Ave', 'Omaha', 1, '414', 'New Zealand' UNION ALL
	SELECT 132, 'Ap #128-204 Morbi Avenue', 'Neustrelitz', 4, '719', 'New Zealand' UNION ALL
	SELECT 25, 'P.O. Box 175, 7798 Est, Avenue', 'Saint-Marc', 1, '2980', 'New Zealand' 

), cAddress AS(

	SELECT
		ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS AddressID
		,StreetNumber
		,CASE WHEN CHARINDEX(',', AddressLine1) > 0 THEN LEFT(AddressLine1, CHARINDEX(',', AddressLine1) - 1) ELSE AddressLine1 END AS AddressLine1
		,CASE WHEN CHARINDEX(',', AddressLine1) > 0 THEN RIGHT(AddressLine1, LEN(AddressLine1) - CHARINDEX(',', AddressLine1) - 1) ELSE NULL END AS AddressLine2
		,City
		,Region
		,RIGHT('0000' + PostCode, 4) AS PostCode
		,Country
	FROM
		cData

)
MERGE INTO dbo.[Address] AS tgt
USING cAddress AS src
ON tgt.AddressID = src.AddressID

WHEN MATCHED THEN UPDATE
SET StreetNumber = src.StreetNumber, AddressLine1 = src.AddressLine1, AddressLine2 = src.AddressLine2, City = src.City, Region = src.Region, PostCode = src.PostCode, Country = src.Country

WHEN NOT MATCHED THEN INSERT(AddressID, StreetNumber, AddressLine1, AddressLine2, City, Region, PostCode, Country)
VALUES(src.AddressID, src.StreetNumber, src.AddressLine1, src.AddressLine2, src.City, src.Region, src.PostCode, src.Country)
;
GO

SET IDENTITY_INSERT dbo.[Address] OFF;
GO
SET IDENTITY_INSERT dbo.Customer ON;
GO

WITH cCustomer(CustomerID, FirstName, LastName, AddressID) AS (

	SELECT 1, 'Erasmus', 'Cotton', 1 UNION ALL
	SELECT 2, 'Dakota', 'Acosta', 2 UNION ALL
	SELECT 3, 'Brenna', 'Merrill', 3 UNION ALL
	SELECT 4, 'Nasim', 'Boyer', 4 UNION ALL
	SELECT 5, 'Cade', 'Short', 5 UNION ALL
	SELECT 6, 'Chava', 'Shaw', 6 UNION ALL
	SELECT 7, 'Charles', 'Andrews', 7 UNION ALL
	SELECT 8, 'Odessa', 'Ellison', 8 UNION ALL
	SELECT 9, 'Yeo', 'Whitney', 9 UNION ALL
	SELECT 10, 'Iona', 'Zamora', 10 UNION ALL
	SELECT 11, 'Angelica', 'Mccray', 11 UNION ALL
	SELECT 12, 'Rebekah', 'Knapp', 12 UNION ALL
	SELECT 13, 'Karleigh', 'Hardin', 13 UNION ALL
	SELECT 14, 'Kevyn', 'Glover', 14 UNION ALL
	SELECT 15, 'Abra', 'Wyatt', 15 UNION ALL
	SELECT 16, 'Daryl', 'Burgess', 16 UNION ALL
	SELECT 17, 'Harding', 'Wright', 17 UNION ALL
	SELECT 18, 'Gillian', 'Ferguson', 18 UNION ALL
	SELECT 19, 'Christine', 'Miles', 19 UNION ALL
	SELECT 20, 'Noelle', 'Riley', 20 UNION ALL
	SELECT 21, 'Shoshana', 'Stephens', 21 UNION ALL
	SELECT 22, 'Renee', 'Foley', 22 UNION ALL
	SELECT 23, 'Jenette', 'Pruitt', 23 UNION ALL
	SELECT 24, 'Noah', 'Williamson', 24 UNION ALL
	SELECT 25, 'Norman', 'Rosales', 25 UNION ALL
	SELECT 26, 'Nasim', 'Shields', 26 UNION ALL
	SELECT 27, 'Melyssa', 'Camacho', 27 UNION ALL
	SELECT 28, 'Lionel', 'Coffey', 28 UNION ALL
	SELECT 29, 'Noble', 'West', 29 UNION ALL
	SELECT 30, 'Linus', 'Pena', 30 UNION ALL
	SELECT 31, 'Gregory', 'Reeves', 31 UNION ALL
	SELECT 32, 'Kerry', 'Murphy', 32 UNION ALL
	SELECT 33, 'Summer', 'Olsen', 33 UNION ALL
	SELECT 34, 'Jessamine', 'Carlson', 34 UNION ALL
	SELECT 35, 'Macon', 'Benjamin', 35 UNION ALL
	SELECT 36, 'Drew', 'Meyer', 36 UNION ALL
	SELECT 37, 'Gloria', 'Tillman', 37 UNION ALL
	SELECT 38, 'Myra', 'Burris', 38 UNION ALL
	SELECT 39, 'Sylvia', 'Hunter', 39 UNION ALL
	SELECT 40, 'Samuel', 'Oneill', 40 UNION ALL
	SELECT 41, 'Larissa', 'Anthony', 41 UNION ALL
	SELECT 42, 'Ezekiel', 'Valencia', 42 UNION ALL
	SELECT 43, 'Hedda', 'Molina', 43 UNION ALL
	SELECT 44, 'September', 'Donaldson', 44 UNION ALL
	SELECT 45, 'Wendy', 'Dickerson', 45 UNION ALL
	SELECT 46, 'Felicia', 'Small', 46 UNION ALL
	SELECT 47, 'Tana', 'Macdonald', 47 UNION ALL
	SELECT 48, 'Francis', 'Hickman', 48 UNION ALL
	SELECT 49, 'Ivan', 'Willis', 49 UNION ALL
	SELECT 50, 'Yuri', 'Dejesus', 50 UNION ALL
	SELECT 51, 'Iliana', 'Salas', 51 UNION ALL
	SELECT 52, 'Forrest', 'Richardson', 52 UNION ALL
	SELECT 53, 'Igor', 'Little', 53 UNION ALL
	SELECT 54, 'Brent', 'Spears', 54 UNION ALL
	SELECT 55, 'Reagan', 'Rojas', 55 UNION ALL
	SELECT 56, 'Oren', 'Sparks', 56 UNION ALL
	SELECT 57, 'Aretha', 'Sears', 57 UNION ALL
	SELECT 58, 'Francis', 'Buchanan', 58 UNION ALL
	SELECT 59, 'Demetrius', 'Meyers', 59 UNION ALL
	SELECT 60, 'Vivian', 'Gutierrez', 60 UNION ALL
	SELECT 61, 'Noelani', 'Huffman', 61 UNION ALL
	SELECT 62, 'Wynne', 'Potter', 62 UNION ALL
	SELECT 63, 'Naida', 'Walls', 63 UNION ALL
	SELECT 64, 'Nasim', 'Cash', 64 UNION ALL
	SELECT 65, 'Chase', 'Fields', 65 UNION ALL
	SELECT 66, 'Wynter', 'Zamora', 66 UNION ALL
	SELECT 67, 'Blythe', 'Lindsey', 67 UNION ALL
	SELECT 68, 'Ulla', 'Armstrong', 68 UNION ALL
	SELECT 69, 'Jennifer', 'Wilkerson', 69 UNION ALL
	SELECT 70, 'Malcolm', 'Salas', 70 UNION ALL
	SELECT 71, 'Leroy', 'Clements', 71 UNION ALL
	SELECT 72, 'Aladdin', 'Sherman', 72 UNION ALL
	SELECT 73, 'Lois', 'Gilmore', 73 UNION ALL
	SELECT 74, 'Sybill', 'Warren', 74 UNION ALL
	SELECT 75, 'Eleanor', 'Hurst', 75 UNION ALL
	SELECT 76, 'Zahir', 'Butler', 76 UNION ALL
	SELECT 77, 'Carla', 'Rasmussen', 77 UNION ALL
	SELECT 78, 'Chancellor', 'Lee', 78 UNION ALL
	SELECT 79, 'Travis', 'Cantu', 79 UNION ALL
	SELECT 80, 'Kermit', 'Wolfe', 80 UNION ALL
	SELECT 81, 'Roanna', 'Vazquez', 81 UNION ALL
	SELECT 82, 'Vance', 'Webb', 82 UNION ALL
	SELECT 83, 'Coby', 'Hobbs', 83 UNION ALL
	SELECT 84, 'Roth', 'Sellers', 84 UNION ALL
	SELECT 85, 'Palmer', 'Todd', 85 UNION ALL
	SELECT 86, 'Gisela', 'Fitzgerald', 86 UNION ALL
	SELECT 87, 'Hannah', 'Vega', 87 UNION ALL
	SELECT 88, 'Emma', 'Collins', 88 UNION ALL
	SELECT 89, 'Anjolie', 'Michael', 89 UNION ALL
	SELECT 90, 'Ruth', 'Burks', 90 UNION ALL
	SELECT 91, 'Mason', 'Jordan', 91 UNION ALL
	SELECT 92, 'Fatima', 'Goodman', 92 UNION ALL
	SELECT 93, 'Christian', 'Cross', 93 UNION ALL
	SELECT 94, 'Jenna', 'Baird', 94 UNION ALL
	SELECT 95, 'Imogene', 'Ward', 95 UNION ALL
	SELECT 96, 'Dieter', 'Stephenson', 96 UNION ALL
	SELECT 97, 'Hamilton', 'Bruce', 97 UNION ALL
	SELECT 98, 'Yardley', 'Mccall', 98 UNION ALL
	SELECT 99, 'Genevieve', 'Massey', 99 UNION ALL
	SELECT 100, 'Meredith', 'Singleton', 100 
)
MERGE INTO dbo.Customer AS tgt
USING cCustomer AS src
ON tgt.CustomerID = src.CustomerID

WHEN MATCHED THEN UPDATE
SET FirstName = src.FirstName, LastName = src.LastName, AddressID = src.AddressID

WHEN NOT MATCHED THEN INSERT(CustomerID, FirstName, LastName, AddressID)
VALUES(src.CustomerID, src.FirstName, src.LastName, src.AddressID)
;
GO

SET IDENTITY_INSERT dbo.Customer OFF;
GO

GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
    AND (((@@microsoftversion / power(2, 24) = 9)
          AND (@@microsoftversion & 0xffff >= 3024))
         OR ((@@microsoftversion / power(2, 24) = 10)
             AND (@@microsoftversion & 0xffff >= 1600))))
    SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
    BEGIN
        EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
    END


GO
PRINT N'Update complete.';


GO