SQL Training - Basics II

Welcome to part II of the SQL Training series. In this article, we will review how to perform queries with the following functions: INSERT, DELETE, UPDATE, IDENTITY INSERT, TRUNCATE, UPDATE with JOIN.

Tip: If you have not completed the first part of this series, we strongly recommend you go back and do so. 

Before you Start

We'll use the [dbo].[D_Department] table for this tutorial. After each statement, you can SELECT * FROM the table to see the changes.

Attention: The SQL commands below affect the data or structures of the tables directly and execute immediately when run. Some changes may not be easily reversed.

INSERT

 INSERT creates new records in a table.

To add a new record called Operations:

INSERT INTO [dbo].[D_Department]
 ([MemberLabel]
 ,[MemberName])

VALUES
 (N'Operations'
 ,N'Operations');

DELETE

 DELETE removes existing records from a table.

To remove a record called Operations:

DELETE FROM [dbo].[D_Department] WHERE [MemberLabel] = N'Operations';

UPDATE

UPDATE modifies existing records in a table.

To change the input value of all records in the [D_Department] table to 0:

UPDATE [dbo].[D_Department] 
SET [Input] = 0;

To update multiple columns at once:

UPDATE [dbo].[D_Department] 
SET [Input] = 1, 
    [Annotate] = 0;

Or attach a WHERE clause to your query to update specific records:

UPDATE [dbo].[D_Department] 
SET [Input] = 0, 
    [Annotate] = 0
WHERE [MemberId] = 2; 

IDENTITY INSERT

As MemberIds are automatically incremented when inserting new records, IDENTITY_INSERT is a property which allows us to bypass this functionality. 

SET IDENTITY_INSERT [dbo].[D_Department] ON;
SET IDENTITY_INSERT [dbo].[D_Department] OFF;

Note: Always keep MemberIds and MemberLabels consistent when inserting records in Kepion tables. 

TRUNCATE

TRUNCATE removes all data and records from a table without deleting the table itself.

To first create a back-up of the [D_Department] table:

SELECT * INTO [dbo].[USR_D_Department]
FROM [dbo].[D_Department]

To remove all records from a table:

TRUNCATE TABLE [dbo].[D_Department];

To reset the [D_Department] table from a back-up:

TRUNCATE TABLE [dbo].[D_Department];

SET IDENTITY_INSERT [dbo].[D_Department] ON;

INSERT INTO [dbo].[D_Department]
([MemberId],
 [MemberLabel], 
 [MemberName],
 [Input],
 [Annotate],
 [Department Name])
SELECT [MemberId], 
 [MemberLabel],
 [MemberName],
 [Input],
 [Annotate],
 [Department Name]
FROM [dbo].[USR_D_Department]

SET IDENTITY_INSERT [dbo].[D_Department] OFF;

UPDATE with JOIN

We can also UPDATE using a JOIN statement. This allows for more advanced data targeting.

For example, let's try combining the Dimension and Hierarchy tables. We'll set all [Input] at the child level to 1. 

First, we'll use a SELECT * FROM statement to view the two tables:

SELECT [MemberLabel], [Input] 
FROM [dbo].[D_Workflow Organization];

SELECT [MemberLabel], [Input], [Childcount] 
FROM [dbo].[V_H_Workflow Organization_Workflow Organization];

The second table is a Hierarchy table which shows the Parent-Child Relationship of each Member and its number of children.

Let's see what these tables when joined:

SELECT DWO.[MemberLabel], DWO.[Input] 
FROM [dbo].[D_Workflow Organization] DWO 
INNER JOIN [dbo].[V_H_Workflow Organization_Workflow Organization] VHWO
ON DWO.[MemberId] = VHWO.[MemberId]
WHERE VHWO.[ChildCount] = 0;

And lastly, let's write our update statement for the [D_Workflow_Organization] table:

UPDATE DWO
SET [Input] = 1
FROM [dbo].[D_Workflow Organization] DWO
INNER JOIN [dbo].[V_H_Workflow Organization_Workflow Organization] VHWO
ON DWO.[MemberId] = VHWO.[MemberId]
WHERE VHWO.[ChildCount] = 0;

Next Steps

Continue learning about SQL in our SQL Training - Basics III article.