CRUD operations in SQL Server
CRUD operations in SQL Server (column1,column2,….) VALUES (value1,value2,….) We put in the table name demo after the insert into command. Now, supply the values to the listed columns id and name in the VALUES clause. 12345 INSERT INTO dbo.DemoVALUES(1, 'Prashanth'); To insert multiple rows, follow the below syntax 123 INSERT INTO (column1,column2,….) VALUES(value1,value2,…. ),( value1,value2,…. ), (value1,value2,…. )… In the following example, the multiple values are listed within in the parenthesis and each list is separated by a comma delimiter 1234567891011 INSERT INTO dbo.Demo(id, name)VALUES(2, 'Jayaram'),(3, 'Pravitha'); To insert rows from SQL Union clause, follow the below syntax 1234 INSERT INTO (column1,column2,….) SELECT value1,value2,… UNION ALL SELECT value1, value2,… In the following example, the multiple values are listed using SELECT statement and then these values combined and fed to the table using SQL UNION ALL set operator. 123456 INSERT INTO dbo.demo SELECT 4, 'Prarthana' UNION ALL SELECT 5, 'Ambika'; The output lists all the inserted rows from the above samples. Notes: It is mandatory to insert at least all of the required columns, but you don’t have to update a column if those values are not required, or if there is a default value for that column A detailed explanation of SQL insert can be found in the following article: Overview of the SQL Insert statement SQL Insert statement only works against a single table unlike select which can work against multiple tables A detailed explanation of SQL Union clause can be found in the following article: SQL Union overview, usage and examples The SQL select statement allows you to query the tables. It allows you to retrieve specific data, one or more rows from one or more tables. The SQL SELECT statement in a vast majority of the time going to contain names of columns from the table(s) that you would like to get data from. Once you have column names, the table name is required in the FROM clause. Now, in a SELECT list, after every column of data, you’re going to need a comma. So you separate each column with a comma, except, no comma after the last column. We’re going to have the SELECT keyword, column name followed by a comma, column name, and the last column name, no comma, FROM clause followed by table name. In this case, one that will return every row in the Address table. And it will return just the AddressID, AddressLine1,AddressLine2,City, StateProvinceID and PostalCode columns. The SQL SELECT statement uses a wildcard character (*) or asterisk to populate all the columns of the table(s). It provides a way to not have to list every column table(s). That’s by using the asterisk or ‘*’. The output lists all the columns of the Address table. The following SQL going to give me all of the columns 123 USE [AdventureWorks2016];GOSELECT * FROM [Person].[Address]; Next, the FROM Clause is going to have at least a table or it is possible to have multiple tables using SQL Join. In the following example, the Product and SalesOrderDetail tables are listed in the FROM Clause of the Select statement. 123 SELECT *FROM Production.Product AS p JOIN Sales.SalesOrderDetail AS s ON s.ProductID = p.ProductID; Notes: A detailed explanation of a few more SQL SELECT statement scenarios is discussed in the article: Overview of the SQL Order by clause SET Column1=Value1, Column2=Value2,…WHERE The UPDATE keyword is followed by the name of the table or view to be updated, and then the set keyword followed by the column name and the value to be set, be it an expression, default, keyword, or null value. If you’re looking to specify which rows are modified using a search condition, the syntax is as follows: Everything is the same as the previous example, only this time you’ll see the “where” clause followed by an expression. A detailed explanation of SQL Update can be found in the following article: Overview of the SQL update statement WHERE When writing a DELETE statement, you’ll define the target table and also which rows you need to delete from the table. The syntax in its simplest form is the DELETE keyword followed by the table name. In some case without a WHERE clause in the query deletes ALL existing rows from the table. To apply a condition clause to the SQL DELETE statement, use the WHERE clause followed by the expression(s). Note: The detailed explanation of SQL delete can be found in the following article: Overview of the SQL delete statement
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
SQLShack
SQL Server training EspañolCRUD operations in SQL Server
December 10, 2018 by Prashanth Jayaram CRUD operations are foundation operations every database developer and administrator needs to understand. Let’s take a look at how they work with this guide.Introduction
According to Wikipedia… “In computer programming, create, read, update, and delete (CRUD) are the four basic functions of persistent storage. Alternate words are sometimes used when defining the four basic functions of CRUD, such as retrieve instead of read, modify instead of update, or destroy instead of delete. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information; often using computer-based forms and reports. The term was likely first popularized by James Martin in his 1983 book managing the Data-base Environment. The acronym may be extended to CRUDL to cover listing of large data sets which bring additional complexity such as pagination when the data sets are too large to hold easily in memory.” CRUD is an acronym that stands for Create, Read, Update, and Delete. These are the four most basic operations that can be performed with most traditional database systems and they are the backbone for interacting with any database.Getting started
Let’s get started to understand the concepts of CRUD operations in SQL ServerCreate
The first letter of CRUD, ‘C’, refers to CREATE aka add, insert. In this operation, it is expected to insert a new record using the SQL insert statement. SQL uses INSERT INTO statement to create new records within the table. Let us create a simple table named Demo for this example. 1234567 USE AdventureWorks2016;GODROP TABLE IF EXISTS Demo;CREATE TABLE dbo.Demo(id INT, name VARCHAR(100)); SQL Insert starts with the keyword INSERT INTO then specify the table name and the columns that we want to insert. The columns go inside of the parentheses and then we specify a VALUES clause. 123 INSERT INTORead
The second letter of CRUD , ‘R’, refers to SELECT (data retrieval) operation. The word ‘read’ retrieves data or record-set from a listed table(s). SQL uses the SELECT command to retrieve the data. When it comes to executing queries, you can use SQL Server Management Studio or SQL Server Data Tools or sqlcmd, based on your preference. For example, to read related data from the specified table, refer to the below syntax. 1 SELECT * FROMUpdate
The third letter of CRUD, ‘U’, refers to Update operation. Using the Update keyword, SQL brings a change to an existing record(s) of the table. You can refer to the article Overview of SQL Update to learn more about SQL update. When performing an update, you’ll need to define the target table and the columns that need to update along with the associated values, and you may also need to know which rows need to be updated. In general, you want to limit the number of rows in order to avoid lock escalation and concurrency issues. The basic syntax for an update: 123 UPDATEDelete
The last letter of the CRUD operation is ‘D’ and it refers to removing a record from a table. SQL uses the SQL DELETE command to delete the record(s) from the table. You can refer to the article Overview of SQL Delete to learn more about SQL delete operation. For example, to delete related data from the specified table, refer to the below syntax 12 DELETE FROMSummary
So, thus far, we’ve discussed a lot about CRUD operations. It is a termed as the foundation of SQL operations in any database products. We also discussed how to implement CRUD Operations in SQL Server. I would recommend reading SQL Insert, SQL Delete, and SQL Update articles which part of CRUD operations. Implementing the Create, Update, Delete, and insert operations are reasonably simple because they are very similar operations. The most efficient way to implement CRUD operations in SQL is through stored procedures. You can refer to the article Creating and using CRUD stored procedures for further reading. Thanks so much for taking the time to read this article. I hope you found it simple and valuable. Feel free leave the comment below. Author Recent Posts Prashanth JayaramI’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021