Transact-SQL - data insertion. Sql queries for inserting, modifying and deleting data Sql query adding

Youtube

This article is devoted to filling database tables with data, that is, we will study SQL commands for inserting new records. It should be said that recording in a table can be done in two ways:

Method number 1.
Let's try to add an entry about the new country to the countries table. The adding syntax will be as follows:
INSERT INTO table_name (field_1, field_2, ...) VALUES (Value_1, Value_2, ...); Based on our table structure, the SQL query will be like this:
INSERT INTO countries (country_name, acronym_name) VALUES ("Russia", "RU"); This is how we added an entry about the country “Russia” to our table. Everything here should be clear and simple, the main thing is to carefully look at which fields you indicate in the first brackets, and write the values in the same order in the second.

Method number 2.
The second method, in my opinion, is a little simpler, since you see what you assign and to which field. Believe me, if a table has a huge number of columns, then it is very easy to confuse or overlook the order of the fields in the first and the order of the values in the second brackets. The syntax of the second method is:
INSERT INTO table_name SET field_1 = Value_1, field_2 = Value_2, ... ; Let's use this example to add some information to the table. persons, since there are more fields, and the advantage of the second method will immediately be felt:
INSERT INTO persons SET first_name="Ivan", last_name="Dulin", registration_date="2012-06-14", country="1"; Now our table contains the following data:

You probably noticed that we did not specify a value for age, but it turned out to be filled in... Everything is correct - for this field we set the default value as 25. So now our Ivan Dulin is listed in the database with an age of 25 years. It may not be the best example to set a default value for the age field, but you can set such properties to fields such as, for example, the site user rating, or the number of page views. They are initially set to 0.

You should also pay attention to the date format in MySQL: YYYY-MM-DD. If you do not adhere to it, then your entries simply will not be entered into the table.

As you can see, the profession field is filled in as NULL, this is an empty value. For this field, we set the property “optional (NULL)”.

As an addition, consider the following command:
LOAD DATA LOCAL INFILE "D:\zapros.txt" INTO TABLE persons; What do you think we have done now?! And we did the following: added data to the table persons from file requests.txt, which is located on drive D. The information in the file should have the following structure:

The file data structure must meet the following requirements:

Each new entry must be described on a new line
Data must be specified for absolutely all fields. As you can see, we specified the value NULL for the id field, because it is auto-incrementing, so MySQL itself will enter the required value.
Fields are separated from each other by a tab character (Tab key).
The information you enter must match the data types of the specific field. That is, for example, a date in the format YYYY-MM-DD, an integer for integer, etc.

Thus, you have learned how to enter new data into your database tables. To consolidate the material you have studied, enter the following information yourself:

Profession table:

Persons table:

first_name	last_name	age	registration_date	country	profession
Leonid	Bilak	45	2012-06-20	2	1
Yuri	Nazarov	22	2012-06-21	3	1
Alla	Savenko	25	2012-06-22	2	3
Irina	Nikolaeva	31	2012-06-22	1	3

Be sure to add these entries, since we will need them to study the SELECT statement (selecting information from the database), which we will look at in the next (fourth) article on studying SQL queries.

Change queries are used to add (INSERT INTO), delete (DELETE), and update (UPDATE) records in tables.

INSERT INTO command

The INSERT INTO command can be used to add one or more records to the end of a table.

The syntax of the INSERT INTO command is as follows:
INSERT INTO Table_Name [(Field_Name)] VALUES (Data_Values);

For example, let's place in the "Groups" table a record containing data (1, BF-16a, 1, 1) in the corresponding columns (Group Code, Name, Course, Semester).

To do this, request in the format:
INSERT INTO Groups (Group Code, Title, Course, Semester) VALUES (1, BF-16a, 1, 1);
enter the query on the form by executing the "Query Builder" command on the "Creation" tab, in the "SQL Mode" mode from the View menu.

Save the request with the name "Add_1_records". As a result, an unrelated object will appear in the “Navigation Area” - “Add_1_records” (Fig. 1.).

Rice. 1.

After saving the request, you must execute this request by clicking on the “Run” icon. As a result of executing the “Add_1_records” command, the first record will appear in the empty table (Fig. 2.)

Rice. 2.

UPDATE command

The UPDATE command is designed to change existing records in table columns, which allows you to update the values in specified table columns.

The UPDATE command syntax is as follows:
UPDATE Table_Name SET Column_Name = New_Value WHERE Selection_Condition;

The SET clause specifies the names of the columns whose data needs to be changed. The WHERE clause is optional, but it can be used to specify only those rows in the columns (tables) that need to be updated.

Let's create a request to change a record in the "Groups" table of the sql_training_st.mdb database.

For example, let's change an existing entry for the first key field id in the "Name" column of the "Groups" table.

Request in the format:
UPDATE Groups SET Name ="BF-16b" WHERE ID=1;

Save the request with the name "Change_record_1". As a result, the object “Change_record_1” will appear in the “Transition Area” (Fig. 3.).

Rice. 3.

After saving the request, you must execute this request by clicking on the “Run” icon. As a result of executing the “Change_record_1” command, a dialog box will appear in which you must enter the value of the parameter ID=1 and click OK. After these actions, in the “Groups” table the entry in the “Name” field will change from BF-16a to BF-16b (Fig. 4.).

Rice. 4.

DELETE command

The DELETE command is designed to delete records from a table.

Format of a request to delete records:
DELETE FROM Table_Name WHERE Selection_Condition;

Let's create a request to change a record in the "Groups" table of the sql_training_st.mdb database.

For example, let's delete the existing record for the first key field id in all columns (fields) of the "Groups" table.

To do this, request in the format:
DELETE FROM Groups WHERE ID=1;
create a query on the form by executing the "Query Builder" command on the "Creation" tab, in the "SQL Mode" mode from the View menu.

We save the request with the name “Delete_record_1”. As a result, the object “Delete_record_1” will appear in the “Transition Area” (Fig. 5.).

Rice. 5.

After saving the request, you must execute this request by clicking on the “Run” icon. As a result of executing the “Delete_record_1” command, a dialog box will appear in which you must enter the value of the parameter ID=1 and click OK. After these steps, the “Confirm deletion of records” dialog box will open. After confirmation, the data in all fields will be deleted in the "Groups" table, i.e. the entry was deleted (Fig. 6.).

Rice. 6.

It should be noted that to delete data in specific fields of a table, it is advisable to use the UPDATE command and replace the values in the required fields with Null. If you need to delete a table as a whole, you should use the DROP TABLE operator (operator syntax: DROP TABLE Table_Name;).

Let's restore the first record in the "Groups" table by executing the command "Add_1_records", as a result we will restore the first record (Fig. 7.).

Rice. 7.

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 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 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.

You should also pay attention to the date format in MySQL: YYYY-MM-DD. If you do not adhere to it, then your entries simply will not be entered into the table.

As you can see, the profession field is filled in as NULL, this is an empty value. For this field.

The file data structure must meet the following requirements:

Each new entry must be described on a new line
Data must be specified for absolutely all fields. As you can see, we specified the value NULL for the id field, because it is auto-incrementing, so MySQL itself will enter the required value.
Fields are separated from each other by a tab character (Tab key).
The information you enter must match the data types of the specific field. That is, for example, a date in the format YYYY-MM-DD, an integer for integer, etc.

Thus, you have learned how to enter new data into your database tables. To consolidate the material you have studied, enter the following information yourself:

Profession table:

Persons table:

first_name	last_name	age	registration_date	country	profession
Leonid	Bilak	45	2012-06-20	2	1
Yuri	Nazarov	22	2012-06-21	3	1
Alla	Savenko	25	2012-06-22	2	3
Irina	Nikolaeva	31	2012-06-22	1	3

In previous sections, we looked at the work of retrieving data from pre-created tables. Now it’s time to figure out how we can create/delete tables, add new records and delete old ones. For these purposes in SQL There are operators such as: CREATE- creates a table, ALTER- changes the table structure, DROP- deletes a table or field, INSERT- adds data to the table. Let's start getting acquainted with this group of operators from the operator INSERT.

1. Adding entire lines

As the name suggests, the operator INSERT used to insert (append) rows to a database table. Adding can be done in several ways:

- add one full line
- add part of a line
- add query results.

So, to add a new row to a table, we need to specify the table name, list the column names and specify the value for each column using the construct INSERT INTO table_name (field1, field2 ...) VALUES (value1, value2...). Let's look at an example.

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES("6", "1st Street", "Los Angeles", "Harry Monroe", "USA")

You can also change the order of column names, but at the same time you need to change the order of the values in the parameter VALUES.

2. Adding part of the lines

In the previous example, when using the operator INSERT we have explicitly marked the table column names. Using this syntax, we can skip some columns. This means that you enter values for some columns but do not provide them for others. For example:

INSERT INTO Sellers (ID, City, Seller_name) VALUES("6", "Los Angeles", "Harry Monroe")

In this example, we did not specify a value for two columns Address And Country. You can exclude some columns from the statement INSERT INTO, if this allows the table definition. In this case, one of the conditions must be met: this column is defined as valid NULL(absence of any value) or the specified default value in the table definition. This means that if no value is specified, the default value will be used. If you are missing a column from a table that does not allow values to appear in its rows NULL and does not have a default value defined, the DBMS will generate an error message and the row will not be added.

3. Adding selected data

In the previous example, we inserted data into tables by entering them manually in the query. However, the operator INSERT INTO allows us to automate this process if we want to insert data from another table. For this purpose in SQL there is such a construction as INSERT INTO ... SELECT .... This design allows you to simultaneously select data from one table and insert it into another. Let's assume we have another table Sellers_EU with a list of sellers of our goods in Europe and we need to add them to the general table Sellers. The structure of these tables is the same (the same number of columns and the same names), but the data is different. To do this, we can write the following query:

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECTID, Address, City, Seller_name, Country FROM Sellers_EU

You need to pay attention so that the values of internal keys are not repeated (field ID), otherwise an error will occur. Operator SELECT may also include suggestions WHERE to filter data. It should also be noted that the DBMS does not pay attention to the names of the columns contained in the statement SELECT, only the order in which they are arranged is important to her. Therefore, the data in the first specified column that was selected due to SELECT, will be filled in the first column of the table in any case Sellers, specified after the operator INSERT INTO, regardless of the field name.

4. Copying data from one table to another

Often when working with databases, there is a need to create copies of any tables for the purpose of backup or modification. To make a full copy of a table, SQL provides a separate statement SELECT INTO. For example, we need to create a copy of the table Sellers, you will need to write the request as follows:

SELECT * INTO Sellers_new FROM Sellers

Unlike the previous design INSERT INTO ... SELECT ... When data is added to an existing table, the design copies the data to the new table. You can also say that the first construct imports data, and the second exports. When using the design SELECT ... INTO ... FROM ... The following should be considered:

- you can use any sentences in the operator SELECT, such as GROUP BY And HAVING
- you can use a join to add data from multiple tables
- data can only be added to one table, no matter how many tables it was taken from.

Transact-SQL - data insertion. Sql queries for inserting, modifying and deleting data Sql query adding

INSERT INTO command

UPDATE command

DELETE command

1. Adding entire lines

2. Adding part of the lines

3. Adding selected data

4. Copying data from one table to another

Popular articles

Latest articles

Sections

Pages

Special projects

Contacts