Introduction to Insert Statement in SQL Server

Introduction

SQL is a complete data manipulation language used not only for database queries but also to modify and update data in the database. Compared to the complexity of the SELECT statement, which supports SQL queries, the SQL statements that modify and create database contents are somewhat simple.

However, database updates pose challenges for a DBMS beyond those presented by database queries. The DBMS must protect the integrity of the stored data during changes, ensuring that only valid data is introduced into the database.

The DBMS must also coordinate simultaneous updates by multiple users, ensuring that the users and their changes do not interfere.

SQL INSERT statement

This means the SQL INSERT statement adds new rows of data to a table. A single row is the smallest unit of data that can be added to a relational database. A new row of data is typically added to a relational database when the row represents a new entity "appears in the outside world." So, for example, in the table presented in the SELECT example, if a new "customer" began purchasing from our company, we must add a row to the CUSTOMER table to store the customer's information.

If they were to order from our range of supplies, we might add a new row to the ORDERS table to store a record that this order was placed. In either case, the new row is added to maintain the database as an accurate model of the real world.

Syntax

Insert into table_Name (columl list) values (Values for column).

In general, an SQL-based DBMS provides three ways of adding new rows of data to a database:

Single-row SQL INSERT
This statement adds a single new row of data to a table.
Multi-row SQL INSERT
This statement extracts rows of data from another part of the database and adds them to a table. It is commonly used in end-of-month or end-of-year processing when "old" rows of a table are moved to an inactive table.
bulk load
This utility adds data to a table from a file outside the database. It is commonly used to initially load the database or incorporate data downloaded from another computer system or collected from many sites.
Single Row SQL Insert statement
The single-row SQL INSERT statement adds a new row to a table. The INTO clause specifies the table that receives the new row (the target table), and the VALUES clause specifies the data values the new row will contain.

The column list indicates which data value goes into which column of the new row.

Syntax

INSERT INTO employee(ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE)
VALUES (111,'vithal','wadje','[email protected]','India','+914545455454')

Note. that the 111 is not surrounded by ' symbols because these are used when dealing with character types.

The ID in this situation is stored as an integer type. Conceptually, the SQL INSERT statement builds a single row of data that matches the column structure of the table, fills it with the data from the VALUES clause, and then adds the new row to the table.

The rows of the table are unordered, so there is no notion of inserting the row "at the top," "at the bottom," or "between two rows" of the table. After the SQL INSERT statement, the new row is simply a part of the table.

A SELECT statement can be performed on the table later and may include the new row, but it may appear anywhere among the rows of query results.

Single-entry SQL INSERTS are most commonly performed through interactive SQL on tables that don't grow very often. However, data about a new customer, order, etc., are nearly always added to a database through a forms-oriented data entry program. The application layer will generate the SQL required to perform the database elements when the complete data entry and the form are submitted. More on this later.

The purpose of the column list in the SQL INSERT statement is to match the data values in the VALUES clause with the columns to receive them. The list of values and the list of columns must contain the same number of items, and the data type of each value must be compatible with the data type of the corresponding column, or an error will occur. As a convenience, SQL allows you to omit the column list from the SQL INSERT statement when all columns are inserted. When the column list is omitted, SQL automatically generates a list of all table columns in a left-to-right sequence.

Using this shortcut, the previous SQL INSERT statement could be rewritten equivalently as:

INSERT INTO employee VALUES 
(111,'vithal','wadje','[email protected]','India','+914545455454')

One important factor here is that when you omit the column list, the NULL keyword must be used in the values list to explicitly assign NULL values to columns, as shown in the example.

In addition, the sequence of data values must correspond exactly to the sequence of columns in the table. When the column names are explicitly defined in the SQL INSERT statement, it automatically assigns a NULL value to any column whose name is missing from the column list in the SQL INSERT statement.

So, therefore, the following are both correctly syntaxed SQL which perform the same effect:

INSERT INTO employee 
VALUES 
(111,'vithal','wadje','[email protected]','India',NULL)
INSERT INTO employee(ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE)
VALUES (111,'vithal','wadje','[email protected]','India','+914545455454')

During the generation of the table structure, most database systems allow certain columns to be set as NOT NULL. Any attempt to omit data from that column when inserting rows will result in an SQL error.

Multi-Row SQL Insert Statement

This form of the SQL INSERT statement adds multiple rows of data to its target table. In this form of the SQL INSERT statement, the data values for the new rows are not explicitly specified within the statement text. Instead, the source of new rows is a database query specified in the statement.

Syntax

For example, let us consider the situation where we wish to split our employee table into regionalized tables. So if we wanted to populate a table called tmptable, we could copy the information in the following way.

INSERT INTO  tmptable 
(ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE)
SELECT ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE
FROM employee WHERE COUNTRY='India'

We can write this SQL statement in a shorter form using the shorthand discussed in the single-row discussion above.

INSERT INTO tmptable SELECT * FROM employee
WHERE COUNTRY='India'

The SQL INSERT statement identifies the table to receive the new rows and the columns to receive the data, just like the single-row SQL INSERT statement. The remainder of the statement is a query that retrieves data from the employee table. Conceptually, SQL performs the query against the ORDERS table and then inserts the query results, row by row, into the tmptable table.

There were several restrictions regarding multi-row SQL INSERT statements made in the SQL1 standard:

The query cannot contain an ORDER BY clause. It's useless to sort the query results anyway because they're being inserted into a table that is, like all tables, unordered.
The query results must contain the same number of columns as the column list in the SQL INSERT statement (or the entire target table if the column list is omitted). The data types must be compatible, column by column.
The query cannot be the UNION of several different SELECT statements. Only a single SELECT statement may be specified.
The target table of the SQL INSERT statement cannot appear in the FROM clauses of the query or any subqueries that it contains. This prohibits inserting part of a table into itself.

The first two restrictions are structural, but the latter two were included in the standard simply to avoid complexity. As a result, these restrictions were relaxed in the SQL2 and subsequent standards.

The standards now allow UNION and join operations and expressions in the query. They also allow various forms of "self-insertion," where the source table for the data to be inserted and the destination table are the same.

Bulk Load Inserting

Data to be inserted into a database is often downloaded from another computer system or collected from other sites and stored in a sequential file. To load the data into a table, you could write a program with a loop that reads each file record and uses the single-row SQL INSERT statement to add the row. However, the overhead of having the DBMS repeatedly execute single-row SQL INSERT statements can be quite high.

Each vendor's utility provides different features, functions, and commands. For this reason, all commercial DBMS products include a bulk load feature that loads data from a file into a table at high speed. The ANSI/ISO standard does not address this function, and it is usually provided as a standalone utility program rather than as part of the SQL language.

Summary

This article is a detailed explanation of the SQL INSERT statement in SQL Server. I hope this article is helpful for all readers. If you have any suggestions, then please get in touch with me.