-------------------------------
-- 1 - Date and Time Data Types
-------------------------------

USE AdventureWorks
GO

-- 1 - Sample table to store dates
CREATE TABLE dbo.TimeTests
   (  
   col_datetime datetime NULL, 
   col_smalldatetime smalldatetime NULL,  
   col_date date NULL,  
   col_datetime2 datetime2(7) NULL,  
   col_datetimeoffset datetimeoffset(7) NULL,  
   col_time time(7) NULL 
   )  ON [PRIMARY]  
GO  

-- 2 - Populate table
INSERT INTO dbo.TimeTests (col_datetime, col_smalldatetime, col_date, 
col_datetime2, col_datetimeoffset, col_time)  
VALUES (GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE())
GO

-- 3 - Review results
SELECT	col_datetime,
		DATALENGTH(col_datetime),
		col_smalldatetime,
		DATALENGTH(col_smalldatetime), 
		col_date,
		DATALENGTH(col_date), 
		col_datetime2,
		DATALENGTH(col_datetime2), 
		col_datetimeoffset,
		DATALENGTH(col_datetimeoffset), 
		col_time,
		DATALENGTH(col_time)  
FROM dbo.TimeTests 
GO

-- 4 - Alternatives and options in previous SQL Server versions
select convert(varchar, getdate(), 8)  
	-- 19:23:23  
select convert(varchar, getdate(), 9)  
	-- Aug 11 2009  7:23:23:400PM  
select convert(varchar, getdate(), 14)  
	-- 19:23:23:400  

-- 5 - Drop table
DROP TABLE dbo.TimeTests
GO


--------------------
-- 2 - MERGE Command
--------------------
USE AdventureWorks
GO

-- 1 - Create a target table
CREATE TABLE dbo.Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
) 
GO

-- 2 - Insert records into target table
INSERT INTO dbo.Products
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO

-- 3 - Create source table
CREATE TABLE dbo.UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
) 
GO

-- 4 - Insert records into source table
INSERT INTO dbo.UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO

-- 5 - Preliminary Results
SELECT * 
FROM dbo.Products
GO

SELECT * 
FROM dbo.UpdatedProducts
GO

-- 6 - Synchronize the target table with refreshed data from source table

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 

--7 - When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 

-- 8 When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN 
INSERT (ProductID, ProductName, Rate) 
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

-- 9 - When there is a row that exists in target table and same record does not exist in source table
-- then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN 
DELETE
--10 - $action specifies a column of type nvarchar(10) in the OUTPUT clause that returns one of three 
-- values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 
SELECT @@ROWCOUNT;
GO

-- 11 - Final Results
SELECT * 
FROM dbo.Products
GO

SELECT * 
FROM dbo.UpdatedProducts
GO

-- 12 - Drop tables
DROP TABLE dbo.Products
GO

DROP TABLE dbo.UpdatedProducts
GO


------------------
-- 3 - TOP Command
------------------

USE AdventureWorks
GO

-- 1 - Hard coded parameters
SELECT TOP 20 * 
FROM HumanResources.Employee
GO

-- 2 - TOP with parameter
DECLARE @top INT 
SET @top = 10 
SELECT TOP(@top) * 
FROM HumanResources.Employee 
GO

-- 3 - Alternative with ROWCOUNT
DECLARE @top INT 
SET @top = 10 
SET ROWCOUNT @top 
SELECT * FROM HumanResources.Employee 
-- set value to return all rows 
SET ROWCOUNT 0 
GO

-- 4 - INSERT, UPDATE and DELETE options
-- insert example 
DECLARE @top INT 
SET @top = 10 
INSERT TOP(@top) HumanResources.Employee2 
SELECT * 
FROM HumanResources.Employee 
GO

SELECT * 
FROM HumanResources.Employee2 
GO

-- update example 
DECLARE @top INT 
SET @top = 10 
UPDATE TOP(@top) HumanResources.Employee 
SET MaritalStatus = 'S' WHERE EmployeeID < 20 
GO

-- delete example 
DECLARE @top INT 
SET @top = 10 
DELETE TOP(10) HumanResources.Employee 
WHERE EmployeeID < 20 
GO