Task_7_Final, Sql Server

Task_7_Final

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE Customer(
CustomerID int PRIMARY KEY,
CompanyName varchar(255),
ContactName varchar(255),
ContactTitle varchar(255),
Address varchar(255),
City varchar(255),
Region varchar(255),
PostalCode int,
Country varchar(50),
Phone int,
Fax int,
DateJoined datetime,
UpdatedAt datetime,
DateLeft datetime    
);

CREATE TABLE Category(
CategoryID int PRIMARY KEY,  
CategoryName varchar(100),
Description varchar(255),
Picture image,    
CreatedAt datetime,
UpdatedAt datetime    
);

CREATE TABLE Product(
ProductID int PRIMARY KEY,
ProductName varchar(100),
SupplierID int,
CategoryID int FOREIGN KEY REFERENCES Category(CategoryID),
QuantityPerUnit int,
UnitPrice Money,
UnitsInStock int,
UnitsOnOrder int,
ReorderLevel int,
Discontinued bit,
CreatedAt datetime,
UpdatedAt datetime    
);

CREATE TABLE Orders(
OrderID int PRIMARY KEY,
CustomerID int FOREIGN KEY REFERENCES Customer(CustomerID),
EmployeeID int,
OrderDate datetime,
RequiredDate datetime,
ShipDate datetime,
ShipVia int,
Frieght Money,
ShipName varchar(255),
ShipAddress varchar(255),
ShipCity varchar(100),
ShipRegion varchar(50),
ShipPostalCode int,
ShipCountry varchar(255),
CreatedAt datetime,
UpdatedAt datetime    
);

CREATE TABLE OrderDetails(
OrderID int FOREIGN KEY REFERENCES Orders(OrderID),
ProductID int FOREIGN KEY REFERENCES Product(ProductID),
UnitPrice Money,
Quantity int,
Discount Money,
CreatedAt datetime,
UpdatedAt datetime    
);

CREATE TABLE Supplier(
SupplierID int PRIMARY KEY,
ProductID int FOREIGN KEY REFERENCES Product(ProductID),    
SupplierName varchar(255),
ProductName varchar(255),    
TotalQuantity int,
DeliveryDate datetime,
AvailableQuantity int,
CreatedAt datetime,
UpdatedAt datetime    
);

CREATE TABLE WareHouse(
ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
SupplierID int,
SupplierName varchar(255),
TotalQuantity int,
AvailableQuantity int,    
DeliveryDate datetime,
ProductID int,
ProductName varchar(255),
CreatedAt datetime,
UpdatedAt datetime,
InsertedAt datetime    
);

CREATE TABLE ProductDataWareHouse(
ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
ProductID int,
ProductName varchar(255),
SupplierID int,
CategoryID int ,
QuantityPerUnit int,
UnitPrice Money,
UnitsInStock int,
UnitsOnOrder int,
ReorderLevel int,
Discontinued bit,
CreatedAt datetime,
UpdatedAt datetime,
InsertedAt datetime    
);
go
CREATE PROCEDURE transfer_data_dw
AS
INSERT INTO WareHouse(SupplierID,SupplierName,TotalQuantity,AvailableQuantity,DeliveryDate,ProductID,ProductName,CreatedAt,UpdatedAt,InsertedAt)
SELECT SupplierID,SupplierName,TotalQuantity,AvailableQuantity,DeliveryDate,ProductID,ProductName,CreatedAt,UpdatedAt,GETDATE() FROM Supplier;

INSERT INTO ProductDataWareHouse(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued,CreatedAt,UpdatedAt,InsertedAt)
SELECT ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued,CreatedAt,UpdatedAt,GETDATE() FROM Product;
go
EXEC transfer_data_dw;
go


run \| edit \| history \| help	0

Please log in to post a comment.

Advertise here.