How to Perform CRUD Operations in SQL Server

How to Perform CRUD Operations in SQL Server

CRUD Operations stands for Create, Read, Update and Delete, these are the basic functions of any database. In this tutorial I will teach how to perform CRUD in SQL Server.

Create = Create a Table / Insert Records into a Table
Read = Select Records from a Table
Update = Update Records in a Table
Delete = Delete Records from a Table

Create a Student Table

I will first create a Student Table to perform CRUD Operations. This table contains the information of students in a School.

When working with data stored in SQL Server, you will need the help of sp_executesql statement in order to execute dynamic statements and stored procedures.

In the Object Explorer of SQL Server right click on the Databases node and select New Database.

new database

On the new window that opens, give the Database name and press the OK button.

database name

This will create your database.

Next make Student table in this newly created database.

In SQL Server, other than Backup and Restore of database, there is also an option to Script Database with it’s complete data.

Right click on the Tables node of the database and select New Table.

new table
Next create the columns as listed below:
Column Name Data Type Allow Nulls Properties
Id Int No Primary, Identity
Name Varchar(50) No
Address Varchar(100) Yes
Age Int No
Standard Varchar(10) No
[Percent] Decimal(5,2) No
AddedOn Datetime No Default Value or Binding = getdate()
Status Bit No
Make sure the ‘Id’ column is ‘Primary’ and set its ‘Is Identity’ property as ‘Yes’
Id Column is Primary and Identity

For the column AddedOn, set its Default Value or Binding as (getdate()).

Default Value or Binding Student table

This is how the Student table looks like:

Create Table Query

The syntax of Create table Query in SQL Server is:

CREATE TABLE TableName
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
Creating the Student Table using Create Table Query

You can also create the Student table by the below Create Table Query:

CREATE TABLE [dbo].[Student]
(
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    Address varchar(100) NULL,
    Age int NOT NULL,
    Standard varchar(10) NOT NULL,
    [Percent] decimal(5, 2) NOT NULL,
    AddedOn datetime NOT NULL DEFAULT GETDATE(),
    Status bit NOT NULL
)

Insert Data into a Table

To insert data in the Student table I will use SQL Server Insert Statement. The syntax of the Insert Statement is:

INSERT INTO TableName (column1,column2,column3,...)
VALUES (value1,value2,value3,...)

Using the below Insert statement, I can Insert the Records in the Student table:

Insert Into Student(Name,Address,Age,Standard,[Percent],Status) 
Values('James Martin','25 Bedford St. New York City, N.Y. 10014',13,9,72.32,1)
I haven’t used Id column in the insert query as it is auto generated. Similarly ‘AddedOn’ column value gets default values of current date using getdate() function, so I haven’t use it too.

After running the insert query the table will show the inserted record.

inserted record in the table
Custom Paging in SQL Server is a technique to make the data retirval fast and light weight. Here only the need data of the current page is fetched.
Stored Procedure to Insert Records

I can also create a Stored Procedure which will insert a record in the Student table when executed. The Insert Stored Procedure is:

CREATE PROCEDURE SP_InsertStudent 
    @Name       VARCHAR(50),
    @Address    VARCHAR(100),
    @Age        INT,
    @Standard   VARCHAR(10),
    @Percent    DECIMAL(5, 2),
    @Status     BIT
AS
BEGIN
    Insert into Student(Name,Address,Age,Standard,[Percent],Status) Values(@Name,@Address,@Age,@Standard,@Percent,@Status)
END

To execute this stored procedure use the below statement:

EXEC [dbo].[SP_InsertStudent]
     @Name = 'James Martin',
     @Address = '25 Bedford St. New York City, N.Y. 10014',
     @Age = 13,
     @Standard = '9',
     @Percent = 72.32,
     @Status = 1

Select Statement to Read the Records

To read records from a table use Select Statement. To get all records from Student table use:

Select * From Student
Use Where Clause to filter out the records from the Select Statement. Example SELECT * FROM Student WHERE AGE >= 13 will return all students with ages 13 or more.
Stored Procedure for Select Statement

The Stored procedure that returns all Students based on age which is given in the Parameter:

CREATE PROCEDURE SP_SelectStudent 
    @Age    INT
AS
BEGIN
    Select * FROM Student Where Age=@Age
END

In the above Stored Procedure you have to provide the value for the @Age parameter. The Stored Procedure will then returns all the students that have the Age same as @Age value.

Look at the below Statement which executes the Select Procedure. I passed the value 13 to @Age parameter and therefore it will return all students who are 13 years old.

EXEC [dbo].[SP_SelectStudent]
     @Age = 13
executing select stored procedure

Update Statement to Update Records

To update one or more records in a table use Update Statement whose syntax is:

Update TableName SET column1=value1,column2=value2

In the Student table, to Update the standard of the student with Id 2, the SQL Query becomes:

Update Student Set Standard=9 Where Id=2
Update Stored Procedure

I can create a Stored Procedure that will perform the Update on name,address, age, standard, percent and status for a student that has an id given by @Id parameter.

All the values are passed to the parameters of the Stored Procedure.

CREATE PROCEDURE SP_UpdateStudent
    @Id INT, 
    @Name   VARCHAR(50),
    @Address    VARCHAR(100),
    @Age    INT,
    @Standard   VARCHAR(10),
    @Percent    DECIMAL(5, 2),
    @Status BIT
AS
BEGIN
    Update Student Set Name=@Name,Address=@Address,Age=@Age,Standard=@Standard,[Percent]=@Percent,Status=@Status WHERE Id=@Id 
END

Let us execute the above stored procedure to update the student with id ‘2’.

EXEC @return_value = [dbo].[SP_UpdateStudent]
     @Id = 2,
     @Name = 'Jack Clinton',
     @Address = '12 Block, House no 2',
     @Age = 14,
     @Standard = '10',
     @Percent = 73.88,
     @Status = 0

The above execute statement changes the name, address, age, standard, percent and status of that student.

Restore Database SQL Server – Check this tutorial which helps you to restore any Database in SQL Server easily.

The below images show the updated record for the student with id 2:

updated 2nd record of the table

Delete Statement to Delete Records

Through the Delete Statement one or more records can be deleted from the table. It’s syntax is:

Delete From TableName
To delete a Student with Id 1

Use Where Clause with Delete Statement when you want to delete certain records.

The below Query deletes all the students that aged less than 10.

Delete From Student Where Age <10
Stored Procedure to Delete Records

I can also create a Stored Procedure that deletes all the students that are less than a specific age.

This specific age is sent to the parameter of the stored procedure.

CREATE PROCEDURE SP_DeleteStudent
    @Age        INT
AS
BEGIN
    Delete Student WHERE Age< @Age
END

To delete all the student that aged less than 5 year the query becomes:

EXEC SP_DeleteStudent
     @Age = 5
Conclusion

Hope you enjoyed learning the CRUD Operations in SQL Server. Let me know if you have any questions.

SHARE THIS ARTICLE

  • linkedin
  • reddit
yogihosting

ABOUT THE AUTHOR

I hope you enjoyed reading this tutorial. If it helped you then consider buying a cup of coffee for me. This will help me in writing more such good tutorials for the readers. Thank you. Buy Me A Coffee donate