photoshop

Transact-SQL - inserting data. SQL INSERT INTO SELECT, Ms sql insert statement in a function

Hello! 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 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 entries. This manual can be used both to add a single row to a table and to bulk insert data. The INSERT statement requires permission to insert data ( INSERT) to the target table.

There are several ways to use the INSERT statement on the part of the data that needs to be inserted:

Enumeration of specific values to insert;
Specifying a dataset as a SELECT query;
Specifying a dataset as 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 the new records are to be inserted;
The column list is a comma-separated list of the column names of the table into which the data will be inserted;
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 to be inserted separated by commas. They are listed in the order in which the columns are listed 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 call to a procedure to get data to insert into a table. The result set returned by the stored procedure must match the list of columns.

This is 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 can be omitted, in which case their order will be determined based on the actual order of the columns in the table. However, you must remember this order when you specify values to insert or write a select query. Personally, I recommend that you still specify a list of columns in which you plan to add data.

It should also be remembered that in the list of columns and in the list of values, respectively, there must be so-called mandatory columns, these are those that cannot contain the NULL value. If they are not specified, 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 \u200b\u200bthat 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, respectively, let's create it, and we will already 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 a price.

Also in the examples, we will use a procedure that returns a table value to add data to the table, so let's create that 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 certain knowledge of the 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 Builder

First, let's try to add one record and immediately look at the result, i.e. Let's write a query for a selection.

INSERT INTO TestTable(ProductName, Price) VALUES("Computer", 100) GO SELECT * FROM TestTable

You can 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 keyword VALUES and in brackets also, in the same order, separated by commas, we wrote the values \u200b\u200bthat we want to insert.

After the INSERT statement, I wrote a SELECT statement and separated them with a GO command.

Now let's imagine that we need to add some lines. We will write the following query 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 a query.

INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id >

IN this example we wrote a SELECT query that returns data from the TestTable, but not all, but only those with an identifier greater than 2. And the result was inserted into the same TestTable.

As an example of how you can add records to a table without specifying a list of columns, let's write another insert query that does the same thing as the query above, only it does 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 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 notice, I didn’t specify the Id column in all the examples, but we have it, there was no error, since this column has the IDENTITY property, it automatically generates identifiers, so inserting data into such a column simply won’t work.

Example 3 - Adding New Records to a Table Using a Stored Procedure

Now let's insert 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 query, we specify 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 insert ( even if 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 I have everything for now!

Last update: 07/13/2017

To add data, the INSERT command is used, which has the following formal syntax:

INSERT table_name [(column_list)] VALUES (value1, value2, ... valueN)

At the beginning there is an INSERT INTO statement, then in brackets you can specify a list of columns, separated by commas, in which data should be added, and at the end, after the word VALUES, in brackets, the values \u200b\u200badded for the columns are listed.

For example, let's say the following database was created earlier:

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 box:

Note that the values for the columns in parentheses after the VALUES keyword 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 set 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 in which values will be added:

INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ("iPhone 6S", 41000, "Apple")

Here the value is specified for only three columns. And now the values are passed 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 set, or NULL. However, unspecified columns must be nullable or have a DEFAULT attribute.

We can also add multiple 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 default value is used for the column using the DEFAULT keyword or NULL:

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ("Mi6", "Xiaomi", DEFAULT, 28000)

In this case, the default value will be used for the ProductCount column (if it is set, if not, then NULL).

If all columns have a DEFAULT attribute that defines a default value, or are nullable, then you can insert default values for all columns:

INSERT INTO Products DEFAULT VALUES

But if you take the Products table, then such a command will fail, since several fields do not have a DEFAULT attribute and at the same time do not allow null values.

The INSERT statement inserts new records into a table. In this case, the column values can be literal constants, or be the result of a subquery. In the first case, a separate INSERT statement is used to insert each row; in the second case, as many rows as returned by the subquery will be inserted.

The operator syntax is as follows:

INSERT INTO[(,...)]

(VALUES(,…) )

| ( DEFAULT VALUES )

As you can see from the presented syntax, the list of columns is optional (it is said square brackets in the description of the syntax). If it is absent, the list of inserted values must be complete, that is, provide values for all columns of the table. The order of the values must match 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 next operator CREATE TABLE :

CREATE TABLE product

maker char (1 ) NOT NULL ,

model varchar(4) NOT NULL ,

type varchar(7) NOT NULL

Let it be required to add the PC model 1157 of manufacturer B to this table. This can be done by the following statement:

INSERT INTO Product

VALUES ("B" , 1157 , "PC" ) ;

If you specify a list of columns, you can change the "natural" order of their sequence:

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 will be replaced by the default value, PC, when a row is inserted. Note that if no default value is specified for a column in a CREATE TABLE statement and a NOT NULL constraint is specified to prevent the use of NULL in this column table, 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 positive. To do this, instead of explicitly specifying a value, use the reserved word DEFAULT :

INSERT INTO Product_D

VALUES ("B" , 1158 , DEFAULT ) ;

Since all columns have default values, to insert a row with default values one could write:

INSERT INTO Product_D

VALUES (DEFAULT , DEFAULT , DEFAULT ) ;

However, there is a special DEFAULT VALUES construct for this case (see operator syntax), which can be used to rewrite the above operator as

INSERT INTO Product_D DEFAULT VALUES ;

Note that when inserting a row into a table, all restrictions imposed on this table. It could be restrictions primary key or unique index, check constraints of type CHECK , referential integrity constraints. If any restriction is violated, the insertion of the row will be rejected. Consider now the case of using a subquery. Suppose we want 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, the formation of inserted rows manually, firstly, is inefficient, and, secondly, it 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 list of columns would have to be applied either in the INSERT statement, or in 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 set to the default value PC for all inserted rows.

Note that when using a subquery containing a predicate, only those rows will be inserted for which the value of the predicate is TRUE (not UNKNOWN !). In other words, if the type column in the Product table were nullable and that value was present in a number of rows, those rows would not be inserted into the Product_D table.

To overcome the limitation of inserting a single row in an INSERT statement when using a row constructor in a VALUES clause, an artificial trick is to use a subquery that forms a row with a UNION ALL clause. So if we need to insert multiple rows with a single 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 it is guaranteed that there will be no duplicate rows, because in this case there will be no check for duplicates.

It should be noted that inserting multiple tuples using the string constructor is already implemented in Relational database management system (DBMS) developed by Microsoft Corporation.Structured Query Language is a general-purpose 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" ) ,

Command adds rows to table or a view of the main table.

Sql INSERT Command Syntax

Insert Command Syntax

Basic keywords and parameters of the INSERT command

schema- authority identifier, usually the same as the name of some user
table view- the name of the table into which the rows are to be inserted; if a view is specified, then the rows are inserted into the view's main table
subquery_1- a subrequest that the server processes in the same way as the view
column- a column of a table or view into which, for each inserted row, the value from the phrase is entered VALUES or subquery; if one of the table's columns is omitted from this list, the value of the column for the inserted row is the default value of the column defined when the table was created. If the column list is completely omitted, the clause VALUES or the query should define values for all columns in the table
VALUES- defines a string of values to be inserted into the table or view; meaning must be defined in the sentence VALUES for each column in the list of columns
subquery_2- a subquery that returns the rows inserted into the table; the select list of this subquery must have the same number of columns as the list of columns in the assertion

Statement with the phrase VALUES adds a single row to the table. This string contains the values defined by the phrase VALUES.
Approval with subquery instead of the phrase VALUES adds to the table all the rows returned by the subquery. The server is processing 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 refer to any table or view, including the target table of the assertion . 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 determined 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 has been violated and cancels the INSERT statement.
When an INSERT statement is issued, any INSERT trigger defined on the table is included.

INSERT INTO Example 1

INSERT INTO department 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 firm employees whose commissions exceed 25% of revenue to 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 suffices to replace the sentence VALUES to the relevant 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.
Inserting a new row into the table table_name.

INSERT INTO

INSERT INTO Example 2.
Inserting a new row in the table_name table 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 newlines using a single command.
INSERT INTO Example 3.
Insert 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');

In addition to the SELECT statement discussed earlier, Data Manipulation Language (DML) contains three other statements: INSERT, UPDATE, and DELETE. Like the SELECT statement, these three statements operate on either tables or views. This article deals with the INSERT statement, and the other two statements are discussed in the next article.

INSERT Statement inserts rows (or parts of rows) into a table. There are two different forms of this instruction:

The first form of the statement allows you to insert one row (or part of it) into the table. And the second form of the INSERT statement allows you to insert into the table the result set of a SELECT statement or a stored procedure executed by an EXECUTE statement. The stored procedure must return data to be inserted into the table. When used with an INSERT statement, the SELECT statement can select values from a different or the same table into which the data is being inserted, as long as the data types of the corresponding columns are compatible.

For both forms, the data type of each inserted value must be compatible with the data type of the corresponding table column. All string and temporary data must be enclosed in quotation marks; Numeric values do not need to be enclosed in quotation marks.

Single line insert

For both forms of the INSERT statement, an explicit list of columns is optional. The absence of a list of columns is equivalent to specifying all the columns of the table.

DEFAULT VALUES parameter inserts default values for all columns. Columns with a TIMESTAMP data type or IDENTITY property are inserted by default with values automatically generated by the system. For columns of other data types, the corresponding non-null default value is inserted, if any, or NULL in otherwise. If null values are not allowed for a column and no default value is defined for the column, the INSERT statement fails and an appropriate message is displayed.

The following example inserts rows into the Employee table in the SampleDb database, demonstrating how to use the INSERT statement to insert a small amount of data into the database:

USE SampleDb; INSERT INTO Employee VALUES(34990, "Andrey", "Batonov", "d1"); INSERT INTO Employee VALUES(38640, "Aleksey", "Vasin", "d3");

There are two different ways inserting values into a new row. The INSERT statement in the example below explicitly uses the NULL keyword and inserts the NULL value into the appropriate column:

USE SampleDb; INSERT INTO Employee VALUES(34991, "Andrey", "Batonov", NULL);

To insert values into some (but not all) columns of a table, you usually need to explicitly specify those columns. Columns not specified must either allow NULL values or must have a default value defined for them.

USE SampleDb; INSERT INTO Employee(Id, FirstName, LastName) VALUES (34992, "Andrey", "Batonov");

The previous two examples are equivalent. In the Employee table, the only column that allows null values is the DepartmentNumber column, and for all other columns, this value was prohibited by the NOT NULL clause in the CREATE TABLE statement.

Order of values in sentence VALUES INSERT statements may differ from the order specified in the CREATE TABLE statement. In such a case, their order must match the order in which the corresponding columns are listed in the column list. The following is an example of inserting data in a different order from the original:

USE SampleDb; INSERT INTO Employee(DepartamentNumber, LastName, Id, FirstName) VALUES ("d1", "Batonov", 34993, "Andrey");

Inserting Multiple Rows

The second form of the INSERT statement inserts one or more rows selected by the subquery into the table. The example below shows how to insert rows into a table using the second form of the INSERT statement. In this case, a request is made to select the numbers and names of departments located in Moscow, and the resulting set is loaded into new table created earlier.

The new MoscowDepartment table created in the example above has the same columns as the existing Department table, except for the missing Location column. The subquery in the INSERT statement selects all rows in the Department table for which the value of the Location column is Moscow, which are then inserted into the new table created at the beginning of the query.

The example below shows another way to insert rows into a table using the second form of the INSERT statement. In this case, a query is executed to select personnel numbers, project numbers, and project start dates for all employees with the position "Manager" who work on the p2 project, and then load the resulting set into a new table created at the beginning of the query:

USE SampleDb; CREATE TABLE ManagerTeam(EmpId INT NOT NULL, ProjectNumber CHAR(4) NOT NULL, EnterDate DATE); INSERT INTO ManagerTeam(EmpId, ProjectNumber, EnterDate) SELECT EmpId, ProjectNumber, EnterDate FROM Works_on WHERE Job = "Manager";

Before inserting rows using the INSERT statement, the MoscowDepartment and ManagerTeam tables (in the examples above) were empty. If the table already existed and contained data rows, then new rows would be added to it.