Hi all! This article will discuss how you can add data to table at Microsoft SQL Server If you are already at least a little familiar with the T-SQL language, then you probably realized that now we will talk about the INSERT statement, as well as how it can be used to add data to a table.
Let's start, as usual, with a little theory.
INSERT statement in T-SQL
INSERT is a T-SQL instruction that is designed to add data to a table, i.e. creating new records. This instruction can be used both to add a single row to a table and to insert data in bulk. The INSERT statement requires permission to insert data ( INSERT) to the target table.
There are several ways to use the INSERT statement on the piece of data that needs to be inserted:
- Listing specific values to insert;
- Specifying a data set as a SELECT query;
- Specifying a data set in the form of a procedure call that returns tabular data.
Simplified syntax
INSERT [table] ( list of columns...) VALUES ( list of values, … ) Or SELECT sample request Or EXECUTE procedure
- INSERT INTO is a command to add data to a table;
- Table is the name of the target table into which you want to insert new records;
- The column list is a list of column names of the table into which the data will be inserted, separated by commas;
- VALUES is a table value constructor with which we specify the values that we will insert into the table;
- The list of values is the values that will be inserted, separated by commas. They are listed in the order that the columns appear in the column list;
- SELECT is a query to select data to insert into a table. The result set that the query returns must match the list of columns;
- EXECUTE is a procedure call to obtain data for insertion into a table. The result set that the stored procedure returns must match the list of columns.
This is roughly what the simplified syntax of the INSERT INTO statement looks like; in most cases, this is how you will add new records to tables.
The list of columns into which you will insert data does not need to be written, in which case their order will be determined based on the actual order of the columns in the table. You must remember this order when you specify values to insert or write a query to select. Personally, I recommend that you still indicate a list of columns into which you plan to add data.
You should also remember that the list of columns and the list of values, respectively, must contain so-called required columns; these are those that cannot contain the NULL value. If you do not specify them, and the column does not have a default value, an error will occur.
I would also like to note that the data type of the values that you will insert must match the data type of the column into which this value will be inserted, or at least support implicit conversion. But I advise you to control the data type ( format) values, both in the list of values and in the SELECT query.
Enough theory, let's move on to practice.
Initial data
In order to add data to the table, we need the table itself, so let's create it and try to add records to it.
Note! All examples will be run in Microsoft SQL Server 2016 Express.
CREATE TABLE TestTable( IDENTITY(1,1) NOT NULL, (100) NOT NULL, NOT NULL)
Our test table will contain a list of products with prices.
Also in the examples we will use a procedure that returns table value, to add data to the table, so let's create one too.
CREATE PROCEDURE TestProcedure AS BEGIN SELECT ProductName, Price FROM TestTable END
For example, it will return data from the newly created TestTable table.
Note!
As you understand, reading this material implies having some knowledge of T-SQL language, so if something is not clear to you, I recommend that you familiarize yourself with the following materials:
Example 1 – Adding a new record to a table using the table value constructor
First let's try adding one record and immediately look at the result, i.e. Let's write a request for a sample.
INSERT INTO TestTable(ProductName, Price) VALUES ("Computer", 100) GO SELECT * FROM TestTable
You see that after the table name we listed the names of the columns to which we will add data, separated by commas, then we indicated the keyword VALUES and in brackets also, in the same order, separated by commas, we wrote the values that we want to insert.
After the INSERT statement, I wrote a SELECT statement and separated them with a GO statement.
Now let's imagine that we need to add a few lines. We will write the following request for this.
INSERT INTO TestTable(ProductName, Price) VALUES ("Computer", 100), ("Keyboard", 20), ("Monitor", 50) GO SELECT * FROM TestTable
Example 2 - Adding new rows to a table using a SELECT query
Very often there is a need to add a lot of data to a table, for example, based on a select query, i.e. SELECT. To do this, instead of VALUES, we just need to specify the request.
INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id >
IN in this example we wrote a SELECT query that returns data from the TestTable table, but not all of it, but only those with an ID greater than 2. And the result was inserted into the same TestTable table.
As an example of how you can add records to a table without specifying a list of columns, let's write another data insertion query that will do exactly the same thing as the query above, only it will not list the columns to insert.
INSERT INTO TestTable SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable
In this case, we are sure that in the TestTable table the first column is ProductName, and the second is Price, so we can afford to write it that way. But, again, in practice it is better to specify a list of columns.
If you noticed, in all the examples I did not specify the Id column, but we have it, no errors occurred, since this column has the IDENTITY property, it automatically generates identifiers, so inserting data into such a column simply cannot be done.
Example 3 - Adding new records to a table using a stored procedure
Now let's insert the data into the table that the stored procedure will return to us. The meaning here is the same, instead of VALUES and instead of a request we indicate a procedure call. But as you understand, the order and number of columns returned by the procedure must strictly match the list of columns to be inserted ( even if the column list is not specified).
INSERT INTO TestTable(ProductName, Price) EXEC TestProcedure GO SELECT * FROM TestTable
I hope this material helped you understand the instructions. INSERT INTO, and that’s all I have for now!
Using SQL, you can copy information from one table to another.
The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.
SQL INSERT INTO SELECT statement,
INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are not changed.
SQL INSERT INTO SELECT, Syntax
We can copy all the columns from one table to another, existing table:
INSERT INTO table2
SELECT * FROM table1;
Or we can copy only the columns we want to another, existing table:
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
Demo version of the database
In this tutorial we will use the well known Northwind database.
Below is a selection from the "Customers" table:
| User ID | Client's name | The contact person | Address | city | Postcode | A country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitucion 2222 | Mexico D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Mexico D.F. | 05023 | Mexico |
And the selection from the "Suppliers" table:
SQL INSERT INTO SELECT, Examples
Copying only a few columns from "Suppliers" Into "Customers":
Copying only German suppliers to "Customers".
The INSERT statement inserts new records into a table. In this case, the column values can be literal constants, or be the result of executing a subquery. In the first case, a separate INSERT statement is used to insert each row; in the second case, as many rows will be inserted as are returned by the subquery.
The operator syntax is as follows:
| (DEFAULT VALUES)
INSERT INTO [ (,...) ]
(VALUES(,…))
As you can see from the presented syntax, the list of columns is optional (the square brackets in the syntax description indicate this). If it is missing, the list of inserted values must be complete, that is, provide values for all columns of the table. In this case, the order of the values must correspond to the order specified by the CREATE TABLE statement for the table into which the rows are inserted. In addition, these values must be of the same data type as the columns in which they are entered. As an example, consider inserting a row into the Product table created by the following CREATE TABLE statement:
CREATE TABLE product
maker char (1) NOT NULL,
model varchar(4) NOT NULL,
type varchar(7) NOT NULL
Suppose you want to add the PC model 1157 from manufacturer B to this table. This can be done with the following statement:
INSERT INTO Product
VALUES ("B" , 1157 , "PC" ) ;
If you specify a list of columns, you can change their “natural” order:
INSERT INTO Product (type, model, maker)
VALUES ("PC" , 1157 , "B" ) ;
It would seem that this is a completely unnecessary feature, which only makes the design more cumbersome. However, it wins if the columns have default values. Consider the following table structure:
CREATE TABLE product_D
maker char (1) NULL,
model varchar(4) NULL,
type varchar (7 ) NOT NULL DEFAULT "PC"
Note that here the values of all columns have default values (the first two are NULL and the last column is type - PC). Now we could write:
INSERT INTO Product_D (model, maker)
VALUES(1157, "B");
In this case, the missing value when inserting a row will be replaced by the default value - PC. Note that if a column is not given a default value in the CREATE TABLE statement and a NOT NULL constraint is specified, which prohibits the use of NULL in this column tables, the default value is NULL .
The question arises: is it possible not to specify a list of columns and, nevertheless, use the default values? The answer is yes. To do this, instead of explicitly specifying the value, use reserved word DEFAULT:
INSERT INTO Product_D
VALUES ("B" , 1158 , DEFAULT ) ;
Since all columns have default values, to insert a row with default values you could write:
INSERT INTO Product_D
VALUES(DEFAULT, DEFAULT, DEFAULT);
However, for this case there is a special construction DEFAULT VALUES (see operator syntax), with which the above operator can be rewritten in the form
INSERT INTO Product_D DEFAULT VALUES ;
Note that when inserting a row into the table, all restrictions imposed on this table. These could be primary key or unique index constraints, CHECK constraints, or referential integrity constraints. If any constraint is violated, the row insertion will be rejected. Let's now consider the case of using a subquery. Suppose we need to insert into the Product_D table all rows from the Product table related to models personal computers(type = 'PC'). Since the values we need are already in some table, manually generating inserted rows is, firstly, ineffective and, secondly, may allow input errors. Using a subquery solves these problems:
The use of the “*” symbol in the subquery is justified in this case, since the order of the columns is the same for both tables. If this were not the case, a column list would have to be applied in either the INSERT statement, the subquery, or both, which would match the order of the columns:
Here, as before, you can specify not all columns if you want to use the existing default values, for example:
In this case, the type column of the Product_D table will be substituted with the default value PC for all inserted rows.
Note that when using a subquery containing a predicate, only those rows for which the predicate value is TRUE (not UNKNOWN !) will be inserted. In other words, if the type column in the Product table were NULLable, and that value was present in a number of rows, then those rows would not be inserted into the Product_D table.
The artificial technique of using a subquery that forms a row with the UNION ALL clause allows you to overcome the limitation on inserting one row in the INSERT statement when using the row constructor in the VALUES clause. So if we need to insert several rows using one INSERT statement, we can write:
INSERT INTO Product_D
SELECT "B" AS maker, 1158 AS model, "PC" AS type
UNION ALL
SELECT "C" , 2190 , "Laptop"
UNION ALL
SELECT "D" , 3219 , "Printer" ;
Using UNION ALL is preferable to UNION even if the absence of duplicate rows is guaranteed, since in this case no check will be performed to eliminate duplicates.
It should be noted that inserting multiple tuples using the row constructor is already implemented in Relational database management system (DBMS), developed by Microsoft Corporation.Structured Query Language) is a universal computer language used to create, modify and manipulate data in relational databases. SQL Server 2008. Given this possibility, the last query can be rewritten as:
INSERT INTO Product_D VALUES
("B", 1158, "PC"),
("C", 2190, "Laptop"),
Team adds rows to the table or main table view.
Sql INSERT Command Syntax
Insert Command Syntax
Basic keywords and parameters of the INSERT command
- schema- permission identifier, usually matching the name of some user
- table view- the name of the table into which the rows should be inserted; if a view is specified, the rows are inserted into the view's main table
- subquery_1- a subquery that the server processes in the same way as a view
- column- a table or view column into which the value from the phrase is entered for each inserted row VALUES or subquery; if one of the table's columns is omitted from this list, the column value for the inserted row is the default column value defined when the table was created. If a column list is completely omitted, the clause VALUES or the query must determine values for all columns in the table
- VALUES- defines a string of values that will be inserted into the table or view; the meaning must be defined in the sentence VALUES for each column in the list of columns
- subquery_2- a subquery that returns rows inserted into the table; the select list of this subquery must have the same number of columns as the statement column list
Statement with the phrase VALUES adds a single row to the table. This line contains the values defined by the phrase VALUES.
Statement with subquery instead of a phrase VALUES adds all the rows returned by the subquery to the table. The server processes subquery and inserts each returned row into the table. If the subquery does not select any rows, the server does not insert any rows into the table.
Subquery can access any table or view, including the target assertion table . The server assigns values to fields in new rows based on the internal position of the columns in the table and the order of the phrase values VALUES or in the query selection list. If any columns are missing from the column list, the server assigns them the default values defined when the table was created. If any of these columns have a NOT NULL constraint then the server returns an error indicating that the constraint was violated and aborts the INSERT statement.
When an INSERT statement is issued, any INSERT trigger defined on the table is enabled.
INSERT INTO Example 1
INSERT INTO dept VALUES(50, "PRODUCTS", "SAN FRANCISCO");
INSERT INTO Customers (city, cname, cnum) VALUES('London', 'Hoffman', 2001);
INSERT INTO Example 2
The following command copies the data of company employees whose commissions exceed 25% of income into the bonus table:
INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal;
INSERT INTO Example 3
If you need to insert NULL-value, you must specify it as a normal value as follows:
INSERT INTO Salespeople VALUES (1001,'Peel',NULL,12);
INSERT INTO Example 4
The command can be used to retrieve values from one table and place them in another using a query. To do this, it is enough to replace the sentence VALUES to the corresponding request:
INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = 'London';
MySQL INSERT
To insert new rows into the database MySQL data used INSERT command, command examples are given below:
INSERT INTO Example 1.
Insert new line to the table table_name.
INSERT INTO
INSERT INTO Example 2.
Inserting a new row into the table table_name indicating the insertion of data into the columns we need.
INSERT INTO table_name VALUES('1','165','0','name');
In the database MySQL It is possible to insert multiple new lines using one command.
INSERT INTO Example 3.
Inserting multiple rows into table table_name.
INSERT INTO table_name (tbl_id, chislo, chislotwo, name) VALUES ('1′,'159′,'34','name1′), ('2′,'14','61','name2′), ('3 ′,'356′,'8′,'name3');
Last update: 07/13/2017
To add data, use the INSERT command, which has the following formal syntax:
INSERT table_name [(column_list)] VALUES (value1, value2, ... valueN)
First comes the INSERT INTO expression, then in parentheses you can specify a comma-separated list of columns to which data should be added, and at the end, after the word VALUES, the values to be added for the columns are listed in parentheses.
For example, suppose the following database was previously created:
CREATE DATABASE productsdb; GO USE productsdb; CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL)
Let's add one line to it using the INSERT command:
INSERT Products VALUES ("iPhone 7", "Apple", 5, 52000)
After successful execution in SQL Server Management Studio, the message "1 row(s) affected" should appear in the message field:
It is worth considering that the values for the columns in brackets after keyword VALUES are passed in the order in which they are declared. For example, in the CREATE TABLE statement above, you can see that the first column is Id. But since the IDENTITY attribute is specified for it, the value of this column is automatically generated and can be omitted. The second column represents ProductName, so the first value, the string "iPhone 7", will be passed to that column. The second value - the string "Apple" will be passed to the third column Manufacturer and so on. That is, the values are passed to the columns as follows:
ProductName: "iPhone 7"
Manufacturer: "Apple"
Also, when entering values, you can specify the immediate columns to which the values will be added:
INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ("iPhone 6S", 41000, "Apple")
Here the value is specified for only three columns. Moreover, now the values are transmitted in the order of the columns:
ProductName: "iPhone 6S"
Manufacturer: "Apple"
For unspecified columns (in this case ProductCount), a default value will be added if the DEFAULT attribute is specified, or a NULL value. However, unspecified columns must be nullable or have a DEFAULT attribute.
We can also add several lines at once:
INSERT INTO Products VALUES ("iPhone 6", "Apple", 3, 36000), ("Galaxy S8", "Samsung", 2, 46000), ("Galaxy S8 Plus", "Samsung", 1, 56000)
In this case, three rows will be added to the table.
Also, when adding, we can specify that the column should have a default value using the DEFAULT keyword, or a NULL value:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ("Mi6", "Xiaomi", DEFAULT, 28000)
In this case, the default value for the ProductCount column will be used (if it is set, if it is not, then NULL).
If all columns have a DEFAULT attribute that specifies a default value, or are nullable, you can insert default values for all columns:
INSERT INTO Products DEFAULT VALUES
But if we take the Products table, then such a command will fail with an error, since several fields do not have the DEFAULT attribute and at the same time do not allow the NULL value.
Loading...
