SQL Training - Basics II – Kepion Support Center

Welcome to part II of the SQL Training series. To follow along with the series, please ensure that you have the Integrated Financial Planning Application installed into Kepion. To download the application, go to the Sample Application page. In this article, we will review how to perform queries with the following functions: INSERT, DELETE, UPDATE, IDENTITY INSERT, TRUNCATE, UPDATE with JOIN.

In this article, we'll use the [dbo].[D_Department] table as an example. After each statement, you can SELECT * FROM the table to see the changes.

Note: 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 is used to modify 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 the same time:

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

Or attach a WHERE clause to specify 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: It's important in Kepion to always keep MemberIds and MemberLabels consistent when inserting records.

TRUNCATE

TRUNCATE will remove all data and records from an existing table, but not delete 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 targeting of data. For example, let's set all [Input] at the child level to 1. Here, we'll combine information from the Dimension table with the Hierarchy table.

Let's first take a look at the two tables we'll use:

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 the number of children. Now let's see what these tables look like joined together:

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;