Anyone, who has ever worked on projects that involve database development or administration, has dealt with CRUD operations in their daily tasks. CRUD stands for CREATE, READ, UPDATE, and DELETE which build up 4 basic operations on data: insertion, retrieval, modification, and removal. For example, this way, developers can perform such tasks as creating, updating, and controlling customer personal data, accounts, payment, and other sensitive information, as well as managing access permissions and rights, optimizing database performance, etc.
In the article, we provide a brief overview of how CRUD operations work in SQL and explain how you can improve data-related tasks with the SQL Complete CRUD generator.
What is CRUD in SQL?
Usually, data is arranged and stored in databases. The widely used database type is relational - it consists of tables with rows and columns populated with data.
CRUD is an acronym defining the main tasks that can be performed on relational databases. In SQL, CRUD operations are the foundation that facilitates database development. CRUD stands for the following:
CREATE means adding or inserting rows into a table
READ means selecting (retrieving) rows from a table
UPDATE means modifying rows in a table
DELETE means removing rows from a table
To begin with, we create the Persons table to illustrate how CRUD operations work in SQL:
CREATE TABLE Persons (
PersonID int PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Examples of using CRUD in SQL
In this section, we provide the syntax of the CREATE, READ, UPDATE, and DELETE operations and examples of their usage in order to better understand how they work in SQL.
CREATE operations
The letter 'C' in the CRUD acronym refers to the CREATE operation that implies adding rows and columns to a table. In SQL Server, the INSERT statement can be used to create new records in the table. The syntax of the INSERT INTO statement would be:
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
Keep in mind that INTO is optional and can be omitted.
As you can see, you need to specify the name of the table to be populated with records and a list of columns along with their values. Now, let’s try to insert data into the Persons table.
CREATE query example
In the example, we add multiple rows to the Persons table by executing the INSERT INTO query:
INSERT INTO dbo.Persons
(
PersonID
,LastName
,FirstName
,Address
,City
)
VALUES
(1,'Conrad','Baumann','1388 Parkwood Loop, STE 31','Lansing'),
(2, 'Adam', 'Reyes', '56 Front Way', 'Salem'),
(3, 'Keira', 'Delacruz', '2676 E Prospect Hill Ave', 'Lincoln');
READ operations
The letter 'R' in the CRUD acronym refers to the READ operation that retrieves data from the table using the SELECT statement. We can either fetch all data from the table or retrieve data filtered by the condition criteria in the WHERE clause.
The syntax of the SELECT statement would be:
-- syntax to retrieve all data from the table
SELECT * FROM table_name;
-- syntax to retrieve filtered data from the table
SELECT * FROM table_name WHERE condition;
READ operation example
After we inserted new records into the Persons table, we can retrieve data to make sure that the rows have been added to the table. To fetch data from the table, we execute the following query:
SELECT * FROM AdventureWorks2019.dbo.Persons;
The result shows that all values for each record in the table have been inserted.
Now, we change the query to view in the output only data that matches the condition in the WHERE clause. For example, we want to see information about Conrad Baumann. To do that, execute the following query:
SELECT
*
FROM AdventureWorks2019.dbo.Persons
WHERE PersonID = 1;
As you can see, the query returned the record according to the specified condition.
Let's go on to have a look at the CRUD UPDATE operation.
UPDATE operations
The letter 'U' in the CRUD acronym refers to the UPDATE operation that modifies the existing records in a SQL table. Thus, to make changes to data, you can use the UPDATE statement with the following syntax:
UPDATE table_name
SET column_1=value_1,column_2=value_2
WHERE condition;
UPDATE query example
Suppose that Conrad Baumann moved to another place and we need to update his address. To modify values, we execute the UPDATE query:
UPDATE AdventureWorks2019.dbo.Persons
SET
Address = '1399 Firestone Drive'
WHERE
PersonID = 1;
To check that the changes have been applied to the table, we execute the SELECT query:
The result in the screenshot verifies that the specified row has been updated.
DELETE operations
The last letter 'D' in the CRUD acronym refers to data deletion. In SQL, it means deleting records from a SQL table by executing the DELETE query. We can remove either all records or specific ones from the table. In the latter case, we can specify which rows to remove in the WHERE clause.
The syntax of the DELETE statement would be:
-- syntax to delete all records from the table
DELETE FROM table_name;
-- syntax to delete specified records from the table
DELETE FROM table_name WHERE condition;
table_name is the table from which data should be deleted.
DELETE statement example
In our example, we need to remove information about Conrad Baumann because he has retired. To do that, we execute the query with the WHERE clause in which we set a condition:
DELETE FROM AdventureWorks2019.dbo.Persons
WHERE
PersonID = 1;
If we retrieve the updated data, we'll see that the specified record has been deleted from the table.
To remove all records from the Persons table, we execute the following DELETE query:
DELETE FROM AdventureWorks2019.dbo.Persons;
This way we considered how CRUD operations work in SQL Server. In order to add, update, or remove data we needed to write statements each time. As you can notice, this may take a lot of time and effort. But that can be worked around and we’ll show how to do it.
CRUD stored procedure generator for SQL Server
To simplify data manipulation and speed up the tasks associated with CRUD operations, you can use the CRUD generator for procedures built into SQL Complete. The tool generates a script of CRUD procedures based on the predefined code templates. Thus, you do not need to write operations each time you want to update or add data. You simply make changes to stored procedures and then execute them. This allows you to perform CRUD operations on SQL tables faster. In addition, you can easily create custom templates up to your requirements. For more information, see How to Create Customizable CRUD Operations with SQL Complete.
Let's see how dbForge SQL Complete generates CRUD procedures. In our example, we will use SSMS. To view CRUD procedure templates, you need to navigate to the SQL Complete menu and select Options. In the dialog that opens, you can manage which statements (SELECT, INSERT, UPDATE, and/or DELETE) to include in the script. If you want to change the template, you need to switch to CRUD > the required statement and then replace placeholders with the actual values.
For example, to add another row for the Persons table using the CRUD generator, in Object Explorer, right-click the Persons table and select SQL Complete > Script Table as CRUD. In the SQL document that opens, execute the generated script with CRUD procedures. Then, in a new SQL document, type the Insert procedure. SQL Complete will output the template of the procedure where you simply need to replace the variables:
After executing the procedure query, retrieve data to make sure that the changes have been applied. If you need to add multiple rows, you simply insert values next to variables and execute the procedure. That’s all - no daunting work required :)
Usage of CRUD in different applications and programming
CRUD operations are widely used not only in relational databases. They can also be used in e-commerce, social media websites, human resources data, etc. Therefore, CRUD can easily be implemented in those applications and businesses that require you to create, update, add, delete, or retrieve data. Since CRUD operations can be involved in many spheres of society and economy, you’d better have at least some basic understanding of CRUD.
Here, we have reviewed the examples of how to deal with CRUD operations in SQL Server. In addition to that, we demonstrated that SQL Complete with its ultimate CRUD procedure generator can streamline and accelerate database development and improve developer productivity.
Try dbForge SQL Complete and boost your productivity!
Try now
We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.
