Task_4_Final, Sql Server

Task_4_Final

Language: Layout:

--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

View schema
Execution time: 0,05 sec, rows selected: 15, rows affected: 10, absolute service time: 0,22 sec, absolute service time: 0,21 sec

	ID	ProductName	Price	CreateDate	ModifyDate
1	1	Milk	50,0000	26.03.2018 01:50:01	26.03.2018 01:50:01
2	2	Cheese	150,0000	26.03.2018 01:50:01	26.03.2018 01:50:01
3	3	Bread	80,0000	26.03.2018 01:50:01	26.03.2018 01:50:01

	ID	ProductID	ProductName	Price	CreateDate	ModifyDate	InsertDate
1	588135f3-d68f-4306-95d7-404543605e74	3	Bread	80	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18
2	fe8bfd38-196c-4707-bdf5-4d3d109f8317	2	Cheese	150	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18
3	c4c0ff4f-ef5d-44b3-ad40-59803d374ab7	1	Milk	50	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18

	ID	ProductName	Price	CreateDate	ModifyDate
1	1	Milk	55,0000	26.03.2018 01:50:01	09.01.2020 21:56:18
2	2	Cheese	150,0000	26.03.2018 01:50:01	26.03.2018 01:50:01
3	3	Bread	80,0000	26.03.2018 01:50:01	26.03.2018 01:50:01

	ID	ProductID	ProductName	Price	CreateDate	ModifyDate	InsertDate
1	588135f3-d68f-4306-95d7-404543605e74	3	Bread	80	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18
2	fe8bfd38-196c-4707-bdf5-4d3d109f8317	2	Cheese	150	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18
3	c4c0ff4f-ef5d-44b3-ad40-59803d374ab7	1	Milk	50	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18
4	e58eafb6-2501-4d55-b8d3-8b8361707f13	2	Cheese	150	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18
5	77256a39-422b-4a1b-858d-c36edd0a7b79	3	Bread	80	26.03.2018 01:50:01	26.03.2018 01:50:01	09.01.2020 21:56:18
6	b475f7f4-b1ca-4654-824e-d498620bd02d	1	Milk	55	26.03.2018 01:50:01	09.01.2020 21:56:18	09.01.2020 21:56:18