SQL Server CRUD Operations

Transact SQL Statements can be classified into DDL, DML, and DCL Statements and in terms of Computer Programming, DML statements can be referred to as CRUD operations. In this article, we can see in more detail the CRUD operations and how we can use those statements in SQL Server with examples.

CRUD Operations:

CRUD is an acronym for CREATE, READ(SELECT), UPDATE, and DELETE statements in SQL Server. CRUD in database terms can be mentioned as Data Manipulation Language (DML) Statements as well. Data Manipulation Language is used to manage or manipulate the data present inside database Tables. Even though CRUD operations are supported on Database object View, Views have several restrictions and hence in this article, we can test the CRUD operations on Tables. Before diving into the individual CRUD Operations or DML Statements in more detail, we can create a table named Employee in the geek's database to test our various CRUD operation-related queries.

Let's first create a database named geeks using the below command in SQL Server Management Studio by opening a new query window:

Query:

CREATE DATABASE geeks;

Output:

In Query Window, connect to the geek's database just created using the below command.

Query:

USE geeks

Now, create a test table named Employee using the below command.

Query:

 CREATE TABLE Employee (EMPId Int Identity NOT NULL,
 EmpNo varchar (10), SSN varchar (10), DOB DATE, 
 CreatedDt Datetime, CreatedBy varchar(10));

CREATE:

CREATE often refers to the INSERT statement which is used to insert new data to any of the SQL Server tables. To INSERT records into a table, we need to use the INSERT command and the syntax for the INSERT command would be:

Query:

INSERT INTO TABLE_NAME (COLUMNS_NAMES_LIST)
VALUES (COLUMNS_VALUES_LIST);

In the above INSERT command syntax, we need to specify the Table Name along with the list of Column Names and a few things to note:

• The list of Column names provided should match the order of the Column values provided.
• The list of Column names specified need not match with the order of the Column names available in the Table.
• If the Columns names list includes the Identity Columns, then we need to specify the SET IDENTITY_INSERT option set to ON before trying to insert values for Identity columns.
• For string and date datatype columns, we need to specify the values within Single quotes.
• In the INSERT command, the Column names list need not be mandatorily specified, however, if not specified, then the values should be specified in the order the columns are available in the table.

Now, let’s try to INSERT some values into the Employee table created above with various possible scenarios as listed below. Default scenarios specifying values in the order columns are present excluding the Identity Datatype column EmpId.

Query: 

INSERT INTO Employee (EmpNO, SSN, DOB, CreatedDt, CreatedBy)
VALUES (1,'1234567890','2000-01-01', GETDATE(), 'system');

Output:

Let’s try Inserting records into the table in a different column order with EmpNo at the end as shown below.

Query:

INSERT INTO Employee (SSN, DOB, CreatedDt, CreatedBy, EmpNO)
VALUES ('0123456789','1999-01-01', GETDATE(), 'system', 2);

Output:

To insert records into the table for the Identity datatype column, we need to use the SET IDENTITY_INSERT option as shown below:

Query:

SET IDENTITY_INSERT Employee ON;
INSERT INTO Employee (EmpId, SSN, DOB, CreatedDt, CreatedBy, EmpNO)
VALUES (3, '0123456789','1999-01-01', GETDATE(), 'system', 2);
SET IDENTITY_INSERT Employee OFF;

Output:

To Insert Multiple values into the Employee column with a single INSERT statement, we need to use the below syntax:

Query:

INSERT INTO Employee (EmpNo, SSN, DOB)
 VALUES 
('4', '1231544984', '2000-02-01')
,('5', '5487946598', '2001-01-01')
,('6', '8789453115', '2002-01-01');

Output:

To Insert records from some other table, we can also use the SELECT statement as shown below:

Query:

INSERT INTO Employee (EmpNo, SSN, DOB)
SELECT EmpNo, SSN, DOB
FROM Employee_history;
 

Output:

While selecting records from some other table, we can also use the TOP Clause or DISTINCT clause, or other criteria.

We can also insert records into a table by passing the results from the execution of Stored Procedure let’s say sp_Employee_History using the below syntax:

Query:

INSERT INTO Employee (EmpNo, SSN, DOB)
EXEC sp_Employee_History;
 

Output:

READ:

READ often refers to SELECT statement or Data Retrieval operation from Tables or Views. The default syntax of a SELECT statement would be:

Query:

SELECT * 
FROM object_name;
 

Where object_name can be a Table or View or functions. We need to specify the braces while trying to SELECT records from functions. The SELECT statement can be combined with various possible operations like:

• Aggregate operations like MIN, MAX, SUM, and COUNT operations with GROUP BY operation.
• SELECT TOP number of records
• SELECT DISTINCT result sets
• SELECT filtered records using WHERE conditions
• HAVING clause to filter records based upon Aggregated columns.
• ORDER BY Clause to sort the result set based upon Selected columns.
   

To SELECT records from Employee Table, we can use the below syntax.
Query: 

SELECT * 
FROM Employee;

Output:

Even though using the * symbol in the SELECT clause returns all columns in the table, it is recommended to explicitly specify the required column names in the SELECT clause to avoid any Application code breakages later due to any changes in Table structure or columns. To SELECT specific columns like EmpNo, SSN, and DOB alone from the Employee table, we can use the below query:

Query:

SELECT EmpNo, SSN, DOB
FROM Employee;

Output:

To SELECT TOP 5 records from Employee Table, we can use the below query.

Query:

SELECT TOP 5 EmpNo, SSN, DOB
FROM Employee;

Output:

To SELECT a unique combination of records from the Employee table for certain columns, we can use the DISTINCT clause along with the columns where we need to fetch the unique values as shown below:

Query:

SELECT DISTINCT EmpNo, SSN
FROM Employee;

Output:

To SELECT specific records from Employee Table, we can use the WHERE clause as shown below:

Query:

SELECT EmpNo, SSN, DOB
FROM Employee
WHERE EmpNo = '1';

Output:

To Aggregate the result set using any Aggregate operation, we would need to use the syntax below:

Query:

SELECT EmpNo, SSN, COUNT(*) cnt
FROM Employee
GROUP BY EmpNo, SSN;

Output:

To Order the above result set based upon EmpNo, we can use the ORDER BY Clause as shown below:

Query:

SELECT EmpNo, SSN, COUNT(*) cnt
FROM Employee
GROUP BY EmpNo, SSN
ORDER BY EmpNo;

Output:

To perform filtering on Aggregated values, we need to use the HAVING clause as shown below. 

Query:

SELECT EmpNo, SSN, COUNT(*) cnt
FROM Employee
GROUP BY EmpNo, SSN
HAVING COUNT(*) = 2
ORDER BY EmpNo;

Output:

To SELECT records from Multiple tables, we would need to use the JOIN clause which can be either INNER JOIN or LEFT OUTER JOIN or RIGHT OUTER JOIN or other types as shown below.

Query:

SELECT e.EmpNo, e.SSN, e.DOB, eh.EmpNo as Older_EmpNo
FROM Employee e
INNER JOIN Employee_history eh on e.EmpNo = eh.EmpNo
WHERE e.EmpNo = 1
ORDER BY e.EmpNo;

Using SELECT statement, we can even create a Table and while creating a table we can create the table with or without data using the below options
 

• Create a Table with Data: 

Query:

SELECT * 
INTO Employee_backup
FROM Employee;

Output:

Selecting from the Backup table will show the records as shown below:

Query:

SELECT * 
FROM Employee_backup;

Output:

• Create Table without Data (Schema only):

Query:

SELECT * 
INTO Employee_backup
FROM Employee
WHERE 1 = 2;

Output:

As we can see above 0 rows are affected indicating no Data changes happened out and selecting from the Backup table will show no records as shown below:

Query:

SELECT * 
FROM Employee_backup;

Output:

UPDATE:

UPDATE operation refers to changing any data existing in the Table and the UPDATE operation or statement in SQL Server includes the SET clause to specify which columns to be updated and use the WHERE clause to UPDATE specific records. The general syntax to update records inside a table will be:

Query:

UPDATE Table_Name
SET column_Name = column_value
WHERE Column_name = Filter_condition;

Output:

To update the record with EmpId = 3 to make EmpNo unique on the Employee table, we can use the below query:

 Query:

UPDATE Employee
SET EmpNo = '3'
, SSN = '4984564512'
, DOB = '1998-01-01';

Upon careful verification of the above query, we can notice that there is no "WHERE condition" specified out, and without a WHERE condition, execution of the above query will UPDATE all the records in the Employee table. Hence, we should be more careful whenever executing an UPDATE statement by ensuring appropriate WHERE clauses as shown below:

Query:

UPDATE Employee
SET EmpNo = '3'
, SSN = '4984564512'
, DOB = '1998-01-01'
WHERE EmpId = 3;

Output:

 

We are able to see the EmpId valued 3 updated successfully with the correct values now. We can also use Common Table Expression to do the equivalent operation using the below query.

 Query:

; with cte as (
SELECT * 
FROM Employee
WHERE EmpId = 3)
UPDATE cte
SET EmpNo = '3'
, SSN = '4984564512'
, DOB = '1998-01-01';

Similar to the INSERT clause, we can also UPDATE only limited records using the TOP clause as shown below:

Query:

UPDATE TOP (5) Employee
SET CreatedDt = GETDATE()
, CreatedBy = 'system'
WHERE CreatedDt IS NULL;

Output:

We can notice that the above UPDATE query has updated only 5 records even though we have a lot of records with CreatedDt as NULL values.
 

Now, let’s create some Default values for CreatedDt and CreatedBy columns using the query below.

Query:

ALTER TABLE Employee ADD CONSTRAINT 
CK_CreatedDt DEFAULT GETDATE() FOR CreatedDt;
ALTER TABLE Employee ADD CONSTRAINT
CK_CreatedBy DEFAULT GETDATE() FOR CreatedBy;

Output:

Now, let’s try to UPDATE all records using the default values with the below query:

Query:

UPDATE Employee
SET CreatedDt = DEFAULT
, CreatedBy = DEFAULT
WHERE CreatedDt IS NULL;

Output:

To UPDATE records in a table using values from another table, the UPDATE statement supports the JOIN clause with another table as shown in the below query. We can specify the alias name of the table that needs to be updated in the UPDATE clause and JOIN any number of tables in the FROM Clause.

Query:

UPDATE e
SET CreatedDt = eh.CreatedDt
, CreatedBy = eh.CreatedBy
FROM Employee e
INNER JOIN Employee_Backup eh on e.EmpId = eh.EmpId;

DELETE:

DELETE operation is used to delete or remove any existing records from the table. Similar to the UPDATE operation, one should be much more careful while executing the DELETE statement and ensure that it has the necessary WHERE clause to avoid accidental deletion of the entire table. 

To DELETE records from a table would be:

Query:

DELETE FROM Table_name
WHERE column_name = Filter_condition;

To Delete records from Employee Table, we can use the below query: 

Query:

DELETE FROM Employee
WHERE EmpId = 10;

Output:

We can use the TOP Clause to delete only limited records for the selected WHERE condition as shown below.

Query:

DELETE TOP(1) FROM Employee
WHERE EmpId = 11;

Output:

We can also use Common Table Expression to delete records with the appropriate WHERE clause like below.
 

Query:

; with cte as (
SELECT * 
FROM Employee
WHERE EmpId = 12
)
DELETE FROM cte;

Output:

Similar to the UPDATE operation, we can also DELETE records from a table by performing a JOIN operation with other tables using the query below.

 Query:

DELETE e
FROM Employee e
JOIN Employee_Backup eh on e.EmpId = eh.EmpId
WHERE e.EmpId = 10;

Output:

CRUD Operations as Stored Procedures
Till now, we have gone through the CRUD operations in detail and to make any changes to these CRUD operations faster in SQL Server, it is recommended to create 4 Stored Procedures one for each table with the procedure name template as shown below
• CREATE/INSERT – Schema_name.Table_name_INSERT
• READ/SELECT – Schema_name.Table_name_SELECT
• UPDATE – Schema_name.Table_name_UPDATE
• DELETE – Schema_name.Table_name_UPDATE
If we have these individual CRUD operations created as Procedures for any logic changes during DML operations, we can simply modify the stored procedures involved without major code changes and avoid Code Rebuild times for most of the cases.
Conclusion
Today, we have understood about CRUD operations and how to perform CRUD operations with various possible syntax in SQL Server with demo as well. In addition to that, we have also discussed about the benefits of having these CRUD operations performed via Stored Procedures which can ease the Development/Enhancement process and during troubleshooting any logic issues as well. In We can use the SQL Complete tool to perform Auto Complete of any Transact SQL Statements or DML statements to improve the Productivity of Developers.