Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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.
Elena-Tests
Outer Apply successer cell chek and get the result
llaves vistas (alumnos) Karen
range and pattern matching
Names
Checking SQL Job run status
text
SQL_RankingFunctions
SO_20171230_SqlServer
Cursor (procedural while)
Please log in to post a comment.