In the database programming, there are four basic and essential operations: create, read, update, and delete. These operations can be defined as the first step in database programming and are called CRUD operations. CRUD is an acronym of the CREATE, READ, UPDATE and DELETE words. The CRUD term was once mentioned by James Martin in his book ‘Managing the Database Environment’ and since then this term has become popular. In this article, we will explore the CRUD operation in terms of SQL Server because the operation syntax can differ from other relational and NoSQL databases. First of all, we will define the database table; a table is a database object which stores data. The table is based on the rows and columns. If you want to create a table, you need at least one column and this column must be of a data type.
The main idea of relational databases is that data is stored in tables. In addition, this table data can be read, inserted, deleted or edited by the CRUD operations. So CRUD operation is basically used to manipulate table data.
CREATE – insert row/rows to table.
READ – read (select) row/rows from a table.
UPDATE – edit row/rows in the table.
DELETE – delete row/rows in the table.
In some approaches, the create table statements are involved in the CREATE operation of the CRUD concept, but as for me, this operation belongs to the DDL (Data Definition Language) statements. As I already noted above, at first we need to create a data table to perform the CRUD operations. Now, we will create a table and this table will contain only three columns: the first column will store country names, the second will store continent of these countries and the last column will store the population of these countries. The following image illustrates the column names and data types of this table. We can create this table with help of the T-SQL statement and entitle it as TblCountry.
CREATE TABLE [dbo].[TblCountry] ( [CountryName] VARCHAR(50), [ContinentNames] VARCHAR(50) NULL, [CountryPopulation] BIGINT NULL )
If we want to add new rows to the table, we have to use the INSERT INTO command. This command starts with the INSERT INTO keyword and then we need to specify the name of the table which we want to perform the insert command and then we will list the column names in brackets. At last, we will add the VALUES keyword and then write values in the specified data types. Pay attention to the string values in double quotes.
INSERT INTO TblCountry (CountryName,ContinentNames,CountryPopulation) VALUES ('Germany','Europe',8279000 )
If we want to add multiple rows to the table, we can use the following type of the INSERT statement.
INSERT INTO TblCountry (CountryName,ContinentNames,CountryPopulation) VALUES ('Germany','Europe',8279000 ), ('Japan','Asia',126800000 ), ('Moroco','Africa',35740000)
Note that the INTO keyword is optional and you don’t need to use it in the insert statements.
INSERT TblCountry (CountryName,ContinentNames,CountryPopulation) VALUES ('Germany','Europe',8279000 ), ('Japan','Asia',126800000 ), ('Moroco','Africa',35740000)
Also, you can use the following format to insert multiple rows to the table.
INSERT INTO TblCountry SELECT 'Germany','Europe',8279000 UNION ALL SELECT 'Japan','Asia',126800000 UNION ALL SELECT 'Moroco','Africa',35740000
Now, we will copy data directly from one source table to another destination table. This method is known as the INSERT INTO … SELECT statement.
INSERT INTO … SELECT requires that the data types of the source and destination tables match. In the following INSERT INTO … SELECT statement, we will insert the data of the SourceCountryTbl table to the TblCountry table. At first, we will insert some synthetic data to the SourceCountryTbl table for this demonstration.
DROP TABLE IF EXISTS [SourceCountryTbl] CREATE TABLE [dbo].[SourceCountryTbl] ( [SourceCountryName] VARCHAR(50), [SourceContinentNames] VARCHAR(50) NULL, [SourceCountryPopulation] BIGINT NULL ) INSERT INTO [SourceCountryTbl] VALUES ('Ukraine','Europe',44009214 ) , ('UK','Europe',66573504) , ('France','Europe',65233271)
Now we will perform the INSERT INTO … SELECT statement.
INSERT INTO TblCountry SELECT * FROM SourceCountryTbl
The above insert statement added all SourceCountryTbl data to the TblCountry table. We can also add the WHERE clause to filter the select statement.
INSERT INTO TblCountry SELECT * FROM SourceCountryTbl WHERE TargetCountryName='UK'
SQL Server allows us to use table variables (table variable is an object which helps to store temporary table data in the local scope) with the INSERT INTO … SELECT statements. In the following demonstration, we will use the table variable as a source table.
DECLARE @SourceVarTable AS TABLE ([TargetCountryName] VARCHAR(50), [TargetContinentNames] VARCHAR(50) NULL, [TargetCountryPopulation] BIGINT NULL ) INSERT INTO @SourceVarTable VALUES ('Ukraine','Europe',44009214 ) , ('UK','Europe',66573504) , ('France','Europe',65233271) INSERT INTO TblCountry SELECT * FROM @SourceVarTable
Tip: Microsoft announced a new feature in SQL Server 2016 which is parallel insert. This feature allows performing the insert operations in the parallel threads. If you add the TABLOCK hint at the end of your insert statement, SQL Server can choose a parallel with the processing execution plan according to your server max degree of parallelism or the cost threshold for parallelism parameters. Parallel insert processing will also reduce the performing time of the insert statement. However, the TABLOCK hint will acquire the lock of the inserted table during the insert operation. For more information about the parallel insert, you can refer to the Real World Parallel INSERT…SELECT.
Another handy statement is the SELECT INTO syntax. This method allows us to copy data from one table into a newly created table. In the following statement, NewCountryTable did not exist before the execution of the query. The query creates the table and also inserts all data from the TblCountry table.
SELECT * INTO NewCountryTable FROM TblCountry
At the same time, we can create a new table for particular columns of the source table.
In some cases, we need to return and use the inserted values from INSERT statement. Since SQL Server 2005, the INSERT statement allows retrieving the values in question from the INSERT statement. Now, we will drop and create our test table and add a new identity column. Additionally, we will also add a default constraint to this column. Thereby if we don’t insert any explicit value to this column, it will automatically create a new value. In the following sample, we will declare a table which has one column and will insert the output of the SeqID column value to this table with help of the OUTPUT column.
DROP TABLE IF EXISTS TblCountry CREATE TABLE [dbo].[TblCountry] ( [CountryName] VARCHAR(50), [ContinentNames] VARCHAR(50) NULL, [CountryPopulation] BIGINT NULL , SeqID uniqueidentifier default(newid()) ) DECLARE @OutputID AS TABLE(LogID uniqueidentifier) INSERT TblCountry (CountryName,ContinentNames,CountryPopulation) OUTPUT INSERTED.SeqId INTO @OutputID VALUES ('Germany','Europe',8279000 ) SELECT * FROM @OutPutId
In the first part of this operation article series, we learned the main concept of the CRUD operations and discussed basic usage forms of the INSERT statement.