Hi all! This article will discuss how you can add data to table in Microsoft SQL Server, if you are already at least a little familiar with the T-SQL language, you probably understood 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 is a T-SQL instruction that is designed to add data to a table, i.e. creating new entries. This statement 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:
Simplified syntax
INSERT [table] ( list of columns...) VALUES ( list of values...) or SELECT sample request Or EXECUTE procedure
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.
You should also remember 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.
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:
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 the 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
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 table, 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.
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!
Let's look at the basic syntax of the SQL INSERT command shown below.
INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);
When supplying the data values to be inserted into the new table, the following should be considered while dealing with different data types.
Example:
Suppose that we have the following list of new library members that need to be added into the database.
| Full names | Date of Birth | gender | physical address | Postal address | contact number | Email address |
|---|---|---|---|---|---|---|
| Leonard Hofstadter | Male | Woodcrest | 0845738767 | |||
| Sheldon Cooper | Male | Woodcrest | 0976736763 | |||
| Rajesh Koothrappali | Male | fairview | 0938867763 | |||
| Leslie Winkle | 14/02/1984 | Male | 0987636553 | |||
| Howard Wolowitz | 24/08/1981 | Male | south park | P.O. Box 4563 | 0987786553 |
Lets" INSERT data one by one. We will start with Leonard Hofstadter. We will treat the contact number as a numeric data type and not enclose the number in single quotes.
INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ("Leonard Hofstadter","Male","Woodcrest",0845738767);
Executing the above script drops the 0 from Leonard's contact number. This is because the value will be treated as a numeric value and the zero (0) at the beginning is dropped since it's not significant.
In order to avoid such problems, the value must be enclosed in single quotes as shown below -
INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ("Sheldon Cooper","Male","Woodcrest", "0976736763");
In the above case , zero(0) will not be dropped
Changing the order of the columns has no effect on the INSERT query as long as the correct values have been mapped to the correct columns.
The query shown below demonstrates the above point.
INSERT INTO `members` (`contact_number`,`gender`,`full_names`,`physical_address`)VALUES ("0938867763","Male","Rajesh Koothrappali","Woodcrest");
The above queries skipped the date of birth column, by default MySQL will insert NULL values in columns that are skipped in the INSERT query.
Let "s now insert the record for Leslie which has the date of birth supplied. The date value should be enclosed in single quotes using the format "YYYY-MM-DD".
INSERT INTO `members` (`full_names`,`date_of_birth`,`gender`,`physical_address`,`contact_number`) VALUES ("Leslie Winkle","1984-02-14","Male","Woodcrest", " 0987636553");
All of the above queries specified the columns and mapped them to values in the insert statement. If we are supplying values for ALL the columns in the table, then we can omit the columns from the insert query.
INSERT INTO `members` VALUES (9,"Howard Wolowitz","Male","1981-08-24","SouthPark","P.O. Box 4563", "0987786553", "lwolowitzemail.me");
Let "s now use the SELECT statement to view all the rows in the members table. SELECT * FROM `members`;
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
| 2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | This email address is being protected from spambots. You need JavaScript enabled to view it. |
| 3 | Robert Phil | Male | 12-07-1989 | 3rd Street 34 | NULL | 12345 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
| 4 | Gloria Williams | Female | 14-02-1984 | 2nd Street 23 | NULL | NULL | NULL |
| 5 | Leonard Hofstadter | Male | NULL | Woodcrest | NULL | 845738767 | NULL |
| 6 | Sheldon Cooper | Male | NULL | Woodcrest | NULL | 976736763 | NULL |
| 7 | Rajesh Koothrappali | Male | NULL | Woodcrest | NULL | 938867763 | NULL |
| 8 | Leslie Winkle | Male | 14-02-1984 | Woodcrest | NULL | 987636553 | NULL |
| 9 | Howard Wolowitz | Male | 24-08-1981 | south park | P.O. Box 4563 | 987786553 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
Notice the contact number for Leonard Hofstadter has dropped the zero (0) from the contact number. The other contact numbers have not dropped the zero (0) at the beginning.
The INSERT command can also be used to insert data into a table from another table. The basic syntax is as shown below.
INSERT INTO table_1 SELECT * FROM table_2;
Let "s now look at a practical example, we will create a dummy table for movie categories for demonstration purposes. We will call the new categories table categories_archive. The script shown below creates the table.
CREATE TABLE `categories_archive` (`category_id` int(11) AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`))
Execute the above script to create the table.
Let "s now insert all the rows from the categories table into the categories archive table. The script shown below helps us to achieve that.
INSERT INTO `categories_archive` SELECT * FROM `categories`;
Executing the above script inserts all the rows from the categories table into the categories archive table. Note the table structures will have to be the same for the script to work. A more robust script is one that maps the column names in the insert table to the ones in the table containing the data .
The query shown below demonstrates its usage.
INSERT INTO `categories_archive`(category_id,category_name,remarks) SELECT category_id,category_name,remarks FROM `categories`;
Executing the SELECT query
SELECT * FROM `categories_archive`
gives the following results shown below.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humor |
| 2 | Romantic | love stories |
| 3 | epic | story ancient movies |
| 4 | Horror | NULL |
| 5 | science fiction | NULL |
| 6 | Thriller | NULL |
| 7 | action | NULL |
| 8 | romantic comedy | NULL |
| 9 | Cartoons | NULL |
| 10 | Cartoons | NULL |
This statement adds one or more records to the table (performs an add query).
Query to add multiple entries:
INSERT INTO target_object [(field1[, field2[, ...]])]
SELECT[ source.]field1[, field2[, ...]
FROM table_expression
Request to add one entry:
INSERT INTO target_object [(field1[, field2[, ...]])]
VALUES ( field1[, field2[, ...])
The INSERT INTO statement consists of the following elements:
Part | Description |
|---|---|
|
target_object |
The name of the table or query where the records are added. |
|
field1, field2 |
After the argument target_object- the names of the fields to which the data is added; after the argument source- the names of the fields from which the data is retrieved. |
|
external_database |
Path to external database. For a description of the path, see the article on the IN clause. |
|
source |
The name of the table or query where the records are copied from. |
|
table_expression |
One or more table names from which to retrieve records. This argument can be a single table name, the result of an INNER JOIN, LEFT JOIN, or RIGHT JOIN expression, or a saved query. |
|
value1, value2 |
Values to be added to specific fields of the new record. Each value is inserted into the field corresponding to its position in the list: value1 added to field1 new record, value2- V field2 etc. You must separate the values with a comma and enclose the text fields in quotation marks (" "). |
An INSERT INTO statement can add a single record to a table using the above syntax. In this case, names and values are specified for each field of the record. You must specify all fields of the record to which values are assigned and their corresponding values. If you don't specify a field value, it will be assigned a default value or NULL. Records are added to the end of the table.
You can also use the INSERT INTO statement to add a set of records from another table or query using the SELECT... FROM clause, as shown above (see Multiple Record Query Syntax). In this case, the SELECT clause specifies the fields to add to the specified target_object.
Source or target_object can be a table or a query. When a query is given, the Microsoft Access database engine adds records to all tables it returns.
The use of the INSERT INTO statement is optional. If present, it must precede the SELECT statement.
If the target table contains a primary key, make sure that the values added to one or more primary key fields are unique and different from NULL; otherwise, no entries will be added.
If records are added to a table with a Count field and you want to renumber them, do not include the Count field in your query. Include the "Counter" field in the query if you want to keep the original values from the field.
You can add records to a table in another database using the IN clause.
To create a table, use the SELECT... INTO statement to get a query to create a table.
Before running an append query, use a select query with the same selection criteria to determine which records will be added based on the results.
An append query copies records from one or more tables to another table. At the same time, the tables containing the added records remain unchanged.
Instead of adding records from another table, you can set the value of each field in a separate new record using the VALUES clause. If the list of fields is omitted, the VALUES clause must include the corresponding values for each field in the table; otherwise, the INSERT operation will fail. Use the INSERT INTO statement along with the VALUES clause for each additional record you want to create.
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 (square brackets in the syntax description indicate this). 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 the following CREATE TABLE statement:
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 no NOT NULL constraint is specified that prohibits the use of NULL in that table column, then the default value of NULL is implied.
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 are checked. These can be primary key or unique index constraints, CHECK type check constraints, 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 personal computer models (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" ) ,
In the previous sections, we considered the work of obtaining 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 like: CREATE- creates a table ALTER- changes the structure of the table, DROP- deletes a table or field, INSERT- adds data to the table. Let's start our acquaintance with this group of operators from the operator INSERT.
As the name suggests, the operator INSERT used to insert (append) rows to a database table. Adding can be done in several ways:
So, to add a new row to the 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 the column names, but at the same time you need to change the order of the values in the parameter VALUES.
In the previous example, when using the operator INSERT we explicitly marked the table column names. Using this syntax, we can skip some columns. This means that you enter a value for some columns but don't provide values 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 it allows the definition of the table. In this case, one of the following conditions must be met: this column is defined as allowing the value NULL(the absence of any value) or in the table definition the specified default value. This means that if no value is specified, the default value will be used. If you skip a column in a table that does not allow values in its rows NULL and does not have a default value defined, the DBMS will issue an error message and this row will not be added.
In the previous examples, we inserted data into tables by writing 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. To do this, SQL has a structure like this: INSERT INTO ... SELECT .... This design allows you to simultaneously select data from one table and insert them into another. Suppose 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 different data. 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 that the value of the internal keys is 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 of their arrangement is important for 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 anyway Sellers specified after the operator INSERT INTO, regardless of the field name.
Often, when working with databases, it becomes necessary to create copies of any tables for the purpose of backup or modification. To make a full copy of a table in SQL, a separate statement is provided 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 construct copies the data to the new table. It can also be said that the first construct imports data, while the second construct exports. When using the structure SELECT ... INTO ... FROM ... the following should be taken into account:
