Task_4_Final, Sql Server

Task_4_Final

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

CREATE TABLE Product(
ID int PRIMARY KEY,
ProductName varchar(100),
Price Money,
CreateDate datetime,
ModifyDate datetime
);

INSERT INTO Product(ID,ProductName,Price,CreateDate,ModifyDate)
VALUES(1,'Milk',50,'2018-03-26 01:50:01.723','2018-03-26 01:50:01.723')
INSERT INTO Product(ID,ProductName,Price,CreateDate,ModifyDate)
VALUES (2,'Cheese',150,'2018-03-26 01:50:01.723','2018-03-26 01:50:01.723')
INSERT INTO Product(ID,ProductName,Price,CreateDate,ModifyDate)
VALUES (3,'Bread',80,'2018-03-26 01:50:01.723','2018-03-26 01:50:01.723');
go
SELECT * FROM Product;
go
CREATE TABLE ProductDataWareHouse(
ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
ProductID int FOREIGN KEY REFERENCES Product(ID),
ProductName varchar(100),
Price int,
CreateDate datetime,
ModifyDate datetime,
InsertDate datetime DEFAULT CURRENT_TIMESTAMP
);
go
INSERT INTO ProductDataWareHouse(ProductID,ProductName,Price,CreateDate,ModifyDate)
SELECT ID,ProductName,Price,CreateDate,ModifyDate FROM Product;
go
SELECT * FROM ProductDataWareHouse
go
UPDATE Product SET Price=55,ModifyDate=CURRENT_TIMESTAMP WHERE ProductName='Milk';
SELECT * FROM Product;
go
CREATE PROCEDURE transfer_data
AS
INSERT INTO ProductDataWareHouse(ProductID,ProductName,Price,CreateDate,ModifyDate)
SELECT ID,ProductName,Price,CreateDate,ModifyDate FROM Product;
go
EXEC transfer_data;
go
SELECT * FROM ProductDataWareHouse;
go


run \| edit \| history \| help	0