Merge Statament

 Merge statement  in SQL Server is used to perform insert, update, or delete operations on a target table based on the results of a join with a source table. It's commonly used for synchronizing two tables. Here's a simple example:

Let's say we have two tables: TargetTable and SourceTable, both with columns ID and Value.

sql

-- Create sample tables
CREATE TABLE TargetTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

CREATE TABLE SourceTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

-- Insert sample data into TargetTable
INSERT INTO TargetTable (ID, Value)
VALUES
    (1, 'Value 1'),
    (2, 'Value 2'),
    (3, 'Value 3');

-- Insert sample data into SourceTable
INSERT INTO SourceTable (ID, Value)
VALUES
    (2, 'Updated Value 2'),
    (3, 'Value 3'),
    (4, 'Value 4');

Now, let's use MERGE to synchronize TargetTable with SourceTable:

sql

-- Merge operation
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)

-- When records are matched, update the values
WHEN MATCHED THEN
    UPDATE SET target.Value = source.Value

-- When there is no match, insert the records
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Value) VALUES (source.ID, source.Value)

-- When there is no match in the source, delete the records in the target
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Display the updated TargetTable
SELECT * FROM TargetTable;

After executing the MERGE statement, the TargetTable will be synchronized with the SourceTable. In this example, the row with ID 2 will be updated with the new value from SourceTable, the row with ID 4 will be inserted into TargetTable, and the row with ID 1 will be deleted from TargetTable because it doesn't exist in SourceTable.

Pivot and Unpivot using SQL and Dynamic SQL

SQL Server, the PIVOT and UNPIVOT operators are used to transform data from rows into columns (PIVOT) or from columns into rows (UNPIVOT).

Here's an example of how you can use PIVOT:

Let's say you have a table Sales with the following columns: Product, Month, and Revenue. You want to pivot the data to show total revenue for each product across different months.

-- Sample data
CREATE TABLE Sales (
    Product VARCHAR(50),
    Month VARCHAR(50),
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (Product, Month, Revenue)
VALUES
    ('Product_A', 'January', 1000),
    ('Product_A', 'February', 1500),
    ('Product_A', 'March', 2000),
    ('Product_B', 'January', 1200),
    ('Product_B', 'February', 1600),
    ('Product_B', 'March', 1800);

-- Pivot query
SELECT *
FROM (
    SELECT Product, Month, Revenue
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Revenue)
    FOR Month IN ([January], [February], [March])
) AS PivotTable;

This query will pivot the data to show total revenue for each product across different months.

And here's an example of how you can use UNPIVOT:

Let's say you have a table Sales_Pivot with the following columns: Product, January, February, March, and you want to unpivot this data to show the revenue for each product in each month.

sql

-- Sample data
CREATE TABLE Sales_Pivot (
    Product VARCHAR(50),
    January DECIMAL(10, 2),
    February DECIMAL(10, 2),
    March DECIMAL(10, 2)
);

INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
    ('Product_A', 1000, 1500, 2000),
    ('Product_B', 1200, 1600, 1800);

-- Unpivot query
SELECT Product, Month, Revenue
FROM (
    SELECT Product, January, February, March
    FROM Sales_Pivot
) AS SourceTable
UNPIVOT (
    Revenue FOR Month IN ([January], [February], [March])
) AS UnpivotTable;

This query will unpivot the data to show the revenue for each product in each month

 Dynamic SQL example

Dynamic SQL in SQL Server allows you to construct SQL statements at runtime, which can be useful for cases where the structure of your pivot or unpivot operation may change dynamically. Here's an example of using dynamic SQL with PIVOT and UNPIVOT:

First, let's start with a dynamic pivot example:

sql

-- Sample data
CREATE TABLE Sales (
    Product VARCHAR(50),
    Month VARCHAR(50),
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (Product, Month, Revenue)
VALUES
    ('Product_A', 'January', 1000),
    ('Product_A', 'February', 1500),
    ('Product_A', 'March', 2000),
    ('Product_B', 'January', 1200),
    ('Product_B', 'February', 1600),
    ('Product_B', 'March', 1800);

-- Dynamic pivot query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month) 
                    FROM Sales
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Product, ' + @cols + ' 
            FROM 
            (
                SELECT Product, Month, Revenue
                FROM Sales
            ) x
            PIVOT 
            (
                SUM(Revenue)
                FOR Month IN (' + @cols + ')
            ) p '

EXEC sp_executesql @query;

This dynamic pivot query will pivot the data to show total revenue for each product across different months.

Now, let's see an example of dynamic unpivot:

sql

-- Sample data
CREATE TABLE Sales_Pivot (
    Product VARCHAR(50),
    January DECIMAL(10, 2),
    February DECIMAL(10, 2),
    March DECIMAL(10, 2)
);

INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
    ('Product_A', 1000, 1500, 2000),
    ('Product_B', 1200, 1600, 1800);

-- Dynamic unpivot query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month) 
                    FROM (VALUES ('January'), ('February'), ('March')) AS Months(Month)
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Product, Month, Revenue
            FROM 
            (
                SELECT Product, January, February, March
                FROM Sales_Pivot
            ) x
            UNPIVOT 
            (
                Revenue FOR Month IN (' + @cols + ')
            ) unpvt'

EXEC sp_executesql @query;

This dynamic unpivot query will unpivot the data to show the revenue for each product in each month.

 

Sample cover Letter, this is the sample letter available in Indeed for IT specialist, and can be used for any IT role with modifications.

Jensen Pope

(111) 789-3456

jensen.pope@email.com

28-Jun-19

Dear Hiring Manager,

I'm happy to be applying to the currently open IT Specialist position at Crossover Software. I have have loved tinkering with computers since childhood and have had an intense desire to pick them apart and learn how they work from the start. This early fascination grew in me over the years and I acquired a strong set of problem-solving skills. I think that with my analytical acumen, I can bring something of value to your company.

I have previous experience working as a software developer for Flagship Computer Engineering. In this role, I not only wrote intricate software, but I also interacted regularly with customers to help them resolve their issues with software products. In addition to this, I have earned A+ and Network+ Certifications from CompTIA, which have given me significant hands-on experience setting up Wi-Fi networks, configuring them, working with routers, adding and removing additional devices to private networks and debugging a wide range of software and hardware issues in the IT world.

Moreover, I am familiar with a broad assortment of malware types and how to remove them or present them from infecting a system. Using what I know, I have been able to work with customers both remotely and in-person to resolve their computer-related difficulties. I thus am just as experienced with the technical end of IT as I am with the customer service end.

I am grateful for your time, kindness and attention in considering me for this position. Working as an IT Specialist at Crossover Software would not only allow me an outlet for what I think are my natural analytical abilities, but it would also allow me to do something I love while providing service and assistance to others. It would be an honor to be able to work as part of the team of excellent IT Specialists and Technicians that you have at Crossover. 

Sincerely,

Jensen Pope 

Logon triggers

As the name suggests  these triggers are invoked at logon event, theses event are raised after authentication but before user session is established



Example : Below example checks count of user session on the server and  rollbacks back if the session count is all ready greater than 100

CREATE TRIGGER limit_100_onnection_trigger 

ON ALL SERVER

FOR LOGON 

AS 

BEGIN 

IF (SELECT COUNT(*) FROM sys.dm_exec_sessions 

            WHERE is_user_process = 1 ) > 100 

    ROLLBACK; 

END;

DDL - Triggers

DDL triggers are kind of triggers which gets  invoked on DDL events such as (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements).

DDL triggers can be defined at Database levels (Database triggers) and server levels (server triggers)

Syntax :

Create Trigger [NAME]

ON [Database | ALL SERVER]

[FOR Event_name ]

As

BEGIN

[SQL CODE]

End

Database scope level example :

CREATE TRIGGER [drop_table]  

ON DATABASE  

FOR DROP_Table 

AS  

   RAISERROR ('can''t drop table',10, 1) 

   ROLLBACK 

GO 

Above trigger will not let any tables to be dropped in the databases

DROP TABLE [dbo].[Person_Audit]

GO

can't drop table

Msg 3609, Level 16, State 2, Line 3

The transaction ended in the trigger. The batch has been aborted.

Server scope trigger  example :

CREATE TRIGGER Create_database_trigger 

ON ALL SERVER  

FOR CREATE_DATABASE  

AS  

    PRINT 'Database Created on ' + cast( getdate() as varchar(30))  

   

GO 

CREATE database [rain_or_shine]

Database Created on May  7 2018  2:08AM

Tiggers

Tiggers are special type of store procedures that gets automatically executed when curtain events occurs on database server

Tiggers are classified into 3 types

• DML triggers
• DDL triggers 
• Logon triggers 

DML triggers gets fired  when users  manipulate the data in table  using (insert, update, delete ) statements

Synatx :

Create Trigger [NAME]

on [TABLE]

[FOR | AFTER | INSTEAD OF]

As

BEGIN

[SQL CODE]

END

DML triggers are of two types  instead of triggers and after trigger
Instead of trigger: Are type of triggers which would  execute  the trigger code instead of DML event (insert delete update)

Instead of insert tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table

CREATE TABLE [dbo].[person](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL

) ON [PRIMARY]

GO

 CREATE TABLE [dbo].[Person_Audit](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL,

       [Audit_info] varchar(30) Null

) ON [PRIMARY]

GO

CREATE Trigger dbo.person_insteadoftrigger_insert

ON  [dbo].[person]

INSTEAD of INSERT

AS

Begin

insert into dbo.person_audit

select [BusinessEntityID],[FirstName],[LastName],'Instead_of_insert'

from inserted

End

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

(1 row affected)

(1 row affected)

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE                   Instead_of_insert

(1 row affected)

Instead of delete tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[person](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL

) ON [PRIMARY]

GO

IF OBJECT_ID('dbo.Person_Audit') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[Person_Audit](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL,

       [Audit_info] [varchar](30) NULL

) ON [PRIMARY]

GO

CREATE trigger dbo.person_insteadoftrigger_delete

ON  [dbo].[person]

INSTEAD of DELETE

AS

Begin

insert into dbo.person_audit

select [BusinessEntityID],[FirstName],[LastName],'Instead_of_delete'

from deleted

End

 INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

DELETE FROM DBO.person

WHERE BusinessEntityID = 1

(1 row affected)

(1 row affected)

We can see below here that, when we have tried to delete the row from the person table, it instead of deleting from person table it inserted a record in our audit table 

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE                   Instead_of_delete

(1 row affected)

Instead of update tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[person](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL

) ON [PRIMARY]

GO

IF OBJECT_ID('dbo.Person_Audit') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[Person_Audit](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL,

       [Audit_info] [varchar](30) NULL

) ON [PRIMARY]

GO

CREATE trigger dbo.person_insteadoftrigger_update

ON  [dbo].[person]

INSTEAD of update

AS

Begin

insert into dbo.person_audit

select [BusinessEntityID],[FirstName],[LastName],'Before_update'

from deleted

UNION

select [BusinessEntityID],[FirstName],[LastName],'After_update'

from inserted

End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

Update dbo.person

set FirstName = 'SQL SERVER'

where BusinessEntityID = 1

(2 rows affected)

(1 row affected)

We can see below here that, when we have tried to update the row from the person table, it instead of updating from person table it inserted a record in our audit table before and after value of what begin updated 

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE                   Instead_of_delete

(1 row affected)

After trigger  is the one which execute trigger code after executing DML action (insert update delete )


After insert tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table

CREATE TABLE [dbo].[person](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL

) ON [PRIMARY]

GO

 CREATE TABLE [dbo].[Person_Audit](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL,

       [Audit_info] varchar(30) Null

) ON [PRIMARY]

GO

CREATE Trigger dbo.person_After_insert

ON  [dbo].[person]

For INSERT

AS

Begin

insert into dbo.person_audit

select [BusinessEntityID],[FirstName],[LastName],'After_insert'

from inserted

End

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

(1 row affected)

(1 row affected)

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE                   After_insert

(1 row affected)

After delete tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[person](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL

) ON [PRIMARY]

GO

IF OBJECT_ID('dbo.Person_Audit') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[Person_Audit](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL,

       [Audit_info] [varchar](30) NULL

) ON [PRIMARY]

GO

CREATE Trigger dbo.person_After_Delete

ON  [dbo].[person]

For DELETE

AS

Begin

insert into dbo.person_audit

select [BusinessEntityID],[FirstName],[LastName],'After_delete'

from deleted

End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

DELETE FROM DBO.person

WHERE BusinessEntityID = 1

(1 row affected)

(1 row affected)

We can see below here that, when we have tried to delete the row from the person table, it deleted from person table and  it inserted a record in our audit table 

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE                   After_delete

(1 row affected)

After update tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[person](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL

) ON [PRIMARY]

GO

IF OBJECT_ID('dbo.Person_Audit') is not null

DROP TABLE [dbo].[person]

GO

CREATE TABLE [dbo].[Person_Audit](

       [BusinessEntityID] [int] NULL,

       [FirstName] [varchar](30) NULL,

       [LastName] [varchar](30) NULL,

       [Audit_info] [varchar](30) NULL

) ON [PRIMARY]

GO

CREATE Trigger dbo.person_After_update

ON  [dbo].[person]

For update

AS

Begin

insert into dbo.person_audit

select [BusinessEntityID],[FirstName],[LastName],'Before_update'

from deleted

union

select [BusinessEntityID],[FirstName],[LastName],'After_update'

from inserted

End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

Update dbo.person

set FirstName = 'SQL SERVER'

where BusinessEntityID = 1

(2 rows affected)

(1 row affected)

We can see below here that, when we have tried to update the row from the person table, it updated row from person table and  it inserted a records in our audit table before and after value of what begin updated 

SELECT * FROM DBO.person

SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName

---------------- ------------------------------ ------------------------------

1                SQL SERVER                     RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info

---------------- ------------------------------ ------------------------------ ------------------------------

1                SANDEEP                        RAIN_OR_SINE                   Before_update

1                SQL SERVER                     RAIN_OR_SINE                   After_update

(2 rows affected)

DDL