title: i do not know how to load source system in SQL
body: <p>This is my task list:</p>
  
  <ol>
  <li>Design a star or snowflake schema for a data warehouse that will help answer various business questions regarding sales. </li>
  <li>Create a database in SQL Server (this is the data warehouse server) based on the star/snowflake schema you designed. </li>
  <li>Load data from the source system i.e. the OLTP database shown earlier to the star/snowflake schema database.  </li>
  <li>The data from most of the tables in the OLTP database have been prepared in a form of SQL statements. Unfortunately, due to company's policy, you are not allowed to load data from the Products and Customer table 'directly'.  </li>
  <li>The Customer data has been prepared in CSV format </li>
  <li>The Product data can only be retrieved from cloud database in JSON format <a href="http://bi.edisonsiow.com/ay1516s2/ca1/getProducts.php" rel="nofollow">http://bi.edisonsiow.com/ay1516s2/ca1/getProducts.php</a> </li>
  <li>You are required to find the most efficient way, which might include coding, to load data from the sources to your SQL Server. </li>
  </ol>
  
  <p>So far i have done this :</p>
  
  <pre><code>CREATE TABLE Customers (
  customerNumber          Int             NOT NULL,
  customerName            Varchar(50)     NOT NULL,
  contactLastName         Varchar(50)     NOT NULL,
  contactFirstName        Varchar(50)     NOT NULL,
  phone                   Varchar (50)    NOT NULL,
  addressLine1            Varchar (50)    NOT NULL,
  addressLine2            Varchar (50)    NOT NULL,
  city                    Varchar (50)    NOT NULL,
  state                   Varchar (50)    NULL,
  postalCode              Varchar (15)    NOT NULL,
  country                 Varchar(50)     NOT NULL,
  salesRepEmployeeNumber  Int             NOT NULL,
  creditLimit             Double          NOT NULL,
  PRIMARY KEY (customerNumber));
  
  
  CREATE TABLE Offices (
  officeCode              Varchar(10)     NOT NULL,
  city                    Varchar(50)     NOT NULL,
  phone                   Varchar(50)     NOT NULL,
  addressLine1            Varchar(50)     NOT NULL,
  addressLine2            Varchar(50)     NOT NULL,
  state                   Varchar(50)     NULL,
  country                 Varchar(50)     NOT NULL,
  postalCode              Varchar(15)     NOT NULL,
  territory               Varchar(10)     NOT NULL,
  PRIMARY KEY (officeCode));
  
  CREATE TABLE payments (
  customerNumber          Int             NOT NULL,
  checkNumber             Varchar(50)     NOT NULL,
  paymentDate             Datetime        NOT NULL,
  amount                  DOUBLE          NOT NULL,
  PRIMARY KEY (customerNumber, checkNumber));
  
  CREATE TABLE OrderDetails (
  orderNumber             Int             NOT NULL,
  productCode             Varchar(15)     NOT NULL,
  quantityOrdered         Int             NOT NULL,
  priceEach               DOUBLE          NOT NULL,
  orderLineNumber         SMALLINT        NOT NULL,
  PRIMARY KEY (orderNumber, productCode));
  
  CREATE TABLE ProductLines (
  productLine             Varchar (50)    NOT NULL,
  textDescription         TEXT            NOT NULL,
  htmlDescription         TEXT            NOT NULL,
  image                   BLOB            NOT NULL,
  PRIMARY KEY (productLine));
  
  CREATE TABLE Orders (
  orderNumber      Int            NOT NULL,
  orderDate        DateTime       NOT NULL,
  requiredDate     DateTime       NOT NULL,
  shippedDate      DateTime       NOT NULL,
  status           Varchar(15)    Not null,
  comments         TEXT           NOT NULL,
  customerNumber   INT            NOT NULL,
  Primary key(orderNumber));
  ALTER TABLE Orders  ADD FOREIGN KEY(customerNumber) REFERENCES Customer(customerNumber);   
  
  CREATE TABLE Employees (
  employeeNumber Int Not null,
  lastName Varchar(50) Not null,
  firstName Varchar(50) Not null,
  extension Varchar(10) NOt null,
  email Varchar(100) Not null,
  officeCode Varchar(10) Not Null,
  reportsTo Int Not null,
  jobTitle Varchar(50) Not null,
  Primary key(employeeNumber));
  
  CREATE TABLE Products ( 
  productCode         Varchar(15)      Not Null,
  productName         Varchar(70)      Not Null,
  productLine         Varchar(50)      Not null,
  productScale        Varchar(10)      Not null,
  productVendor       Varchar(50)      Not null,
  productDescription  TEXT             Not null,
  quantityinStock     Int              Not null,
  Buy Price           Double           Not null,
  MSRP                Double           Not null,
  Primary key (productCode))
  9:28PM
  
  CREATE TABLE payments (
  customerNumber          Int             NOT NULL,
  checkNumber             Varchar(50)     NOT NULL,
  paymentDate             Datetime        NOT NULL,
  amount                  Float           NOT NULL,
  PRIMARY KEY (customerNumber, checkNumber));
  
  
  
  CREATE TABLE Customers (
  customerNumber          Int             NOT NULL,
  customerName            Varchar(50)     NOT NULL,
  contactLastName         Varchar(50)     NOT NULL,
  contactFirstName        Varchar(50)     NOT NULL,
  phone                   Varchar (50)    NOT NULL,
  addressLine1            Varchar (50)    NOT NULL,
  addressLine2            Varchar (50)    NOT NULL,
  city                    Varchar (50)    NOT NULL,
  state                   Varchar (50)    NULL,
  postalCode              Varchar (15)    NOT NULL,
  country                 Varchar(50)     NOT NULL,
  salesRepEmployeeNumber  Int             NOT NULL,
  creditLimit             Float           NOT NULL,
  PRIMARY KEY (customerNumber));
  
  ALTER TABLE Payments  ADD FOREIGN KEY(customerNumber) REFERENCES Customers(customerNumber);  -- here
  
  
  CREATE TABLE Offices (
  officeCode              Varchar(10)     NOT NULL,
  city                    Varchar(50)     NOT NULL,
  phone                   Varchar(50)     NOT NULL,
  addressLine1            Varchar(50)     NOT NULL,
  addressLine2            Varchar(50)     NOT NULL,
  state                   Varchar(50)     NULL,
  country                 Varchar(50)     NOT NULL,
  postalCode              Varchar(15)     NOT NULL,
  territory               Varchar(10)     NOT NULL,
  PRIMARY KEY (officeCode));
  
  
  CREATE TABLE OrderDetails (
  orderNumber             Int             NOT NULL,
  productCode             Varchar(15)     NOT NULL,
  quantityOrdered         Int             NOT NULL,
  priceEach               Float           NOT NULL,
  orderLineNumber         SMALLINT        NOT NULL,
  PRIMARY KEY (orderNumber, productCode));
  
  
  CREATE TABLE ProductLines (
  productLine             Varchar (50)    NOT NULL,
  textDescription         TEXT            NOT NULL,
  htmlDescription         TEXT            NOT NULL,
  image                   Float           NOT NULL,
  PRIMARY KEY (productLine));
  
  
  CREATE TABLE Orders (
  orderNumber      Int            NOT NULL,
  orderDate        DateTime       NOT NULL,
  requiredDate     DateTime       NOT NULL,
  shippedDate      DateTime       NOT NULL,
  status           Varchar(15)    Not null,
  comments         TEXT           NOT NULL,
  customerNumber   INT            NOT NULL,
  Primary key(orderNumber));
  ALTER TABLE OrderDetails  ADD FOREIGN KEY(orderNumber) REFERENCES Orders(orderNumber);   -- here
  
  
  CREATE TABLE Employees (
  employeeNumber Int Not null,
  lastName Varchar(50) Not null,
  firstName Varchar(50) Not null,
  extension Varchar(10) NOt null,
  email Varchar(100) Not null,
  officeCode Varchar(10) Not Null,
  reportsTo Int Not null,
  jobTitle Varchar(50) Not null,
  Primary key(employeeNumber));
  
  
  CREATE TABLE Products ( 
  productCode         Varchar(15)      Not Null,
  productName         Varchar(70)      Not Null,
  productLine         Varchar(50)      Not null,
  productScale        Varchar(10)      Not null,
  productVendor       Varchar(50)      Not null,
  productDescription  TEXT             Not null,
  quantityinStock     Int              Not null,
  BuyPrice            Float            Not null,
  MSRP                Float            Not null,
  Primary key (productCode))
  
  ALTER TABLE OrderDetails  ADD FOREIGN KEY(productCode) REFERENCES Products(productCode);
  </code></pre>
  
  <p>I dont know how to load from source system?</p>