Task_5_Final, Sql Server

Task_5_Final

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

CREATE TABLE Product(
IDProduct int PRIMARY KEY,
Name varchar(100),
Price Money,
CreateDate datetime
);

CREATE TABLE ProductDataWareHouse(
ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
IDProduct int,
Name varchar(100),
Price Money,
CreateDate datetime,
InsertDate datetime
);

CREATE TABLE ID_Table(
ID int PRIMARY KEY,
StartID int,
EndID int,
StartDate datetime,
Endtime datetime    
);
go
INSERT INTO ID_Table(ID,StartID,EndID,StartDate,Endtime)
VALUES (1,1,3,GETDATE(),NULL);

INSERT INTO Product(IDProduct,Name,Price,CreateDate)
VALUES (1,'Milk',10,GETDATE())
INSERT INTO Product(IDProduct,Name,Price,CreateDate)
VALUES (2,'Bread',8,GETDATE())
INSERT INTO Product(IDProduct,Name,Price,CreateDate)
VALUES (3,'Cheese',15,GETDATE());
go
SET NOCOUNT ON;
go
CREATE PROCEDURE add_data_to_product @ID int,@ProductName varchar(100),@Price Money,@InsertDate datetime
AS
DECLARE @counter int;
DECLARE @maxid int;
SET @counter=0;
SELECT @counter=COUNT(*) FROM ID_Table WHERE @ID>=StartID AND @ID<=EndID;
IF @counter=0 
--"{"
UPDATE ID_Table SET Endtime=GETDATE() WHERE Endtime=NULL;
SELECT @maxid=MAX(ID)+1 FROM ID_Table;
INSERT INTO ID_Table(ID,StartID,StartDate)
VALUES (@maxid,@ID,@InsertDate);
--"}"
go
EXEC add_data_to_product @ID=4,@ProductName='Salmon',@Price=10,@InsertDate='01-10-2019'
go
SELECT * FROM ID_Table;
go
CREATE PROCEDURE transfer_data_dw
AS
INSERT INTO ProductDataWareHouse(IDProduct,Name,Price,CreateDate,InsertDate)
SELECT IDProduct,Name,Price,CreateDate,GETDATE() FROM Product WHERE Product.IDProduct<=(SELECT MAX(EndId) FROM ID_table where EndId IS NOT NULL);
go
EXEC transfer_data_dw;
go
SELECT * FROM ProductDataWareHouse;
go


run \| edit \| history \| help	0