SQL Insert Into Table Statement: In our previous blog post, we learned about SQL (Structured Query Language), Which is mainly used to communicate and manipulate the data in the database. But before manipulating the data, the database should have data, so to have some data in the database, we can use the SQL INSERT INTO statement.
The SQL INSERT INTO statement is a useful command that can be used to add single or multiple records into tables in a relational database. We can also use the INSERT INTO SELECT statement to quickly copy data from one table and insert it into another.
SQL How To Insert Statement
If you’re looking to add data into a table in SQL, the best way is using an INSERT command. It’s part of the Data Manipulation Language (DML) that helps create and edit relational databases’ records. To use it correctly, we need to understand how the INSERT INTO statement works – let’s take a look.
What is INSERT Query in SQL?
The SQL INSERT INTO statement can add new tuples to your database’s table. It can be used to insert data in specific columns or for all of them at once, and it allows you to transfer a single row or multiple rows from one table into another. Now that you know what the query does, let’s look into its syntax!
There are two ways to insert data into the table, and that is:
- By SQL INSERT INTO Statement
- By specifying column names
- Without specifying column names
- By SQL INSERT INTO Select Statement
Now we are going to discuss how to insert records into a table by following the above ways:
SQL INSERT INTO TABLE Directly
With the help of the SQL INSERT INTO command, we can insert records into a table. But here are also two ways to insert records.
In the first approach, you don’t have to indicate which column the data should go into – just their values are required. And for this, here is the syntax:
INSERT INTO table_name
VALUES (value1, value2, value3....);
Before updating the product table, it is looking something like this:
Now let us take an example to understand how to insert a record by following SQL INSERT INTO command.
INSERT INTO products VALUES (8, 'SoftwareTestingO', 25);
Note: If the inputted value and number of columns are not matched, you will get an error.
In the second approach, we have to mention the column name and the values for inserting any records into a table using the SQL INSERT INTO command. For that, the syntax will look like the below:
INSERT INTO table_name (column1, column2, column3....)
VALUES (value1, value2, value3.....);
Let us try to understand the above approach with an example.
INSERT INTO products (product_id, product_name, category_id ) VALUES (9, 'Software',75);
After adding records by both approaches, the final table looks like the below:
SQL Insert Into Multiple Rows
It is possible that you need to add more than one row of data. If you’re looking for a way to insert multiple rows into your database quickly, you can follow the process below.
INSERT INTO products (product_id, product_name, category_id ) VALUES (10, 'Testingo',75), (11, 'Manual',50), (12, 'Automation',25);
Insert Records Using SQL Insert Into Select
We can easily copy rows from one table to another using the SELECT statement with INSERT INTO. This process is similar to that of simply inserting into a new table.
You can use the SELECT and INSERT INTO statements to take rows from one table and place them into another. This works similarly to simply using an INSERT INTO statement by itself.
Inserting all columns of a table: We can copy all the data of a table and insert those data into a different table. Here is the syntax:
INSERT INTO first_table SELECT * FROM second_table;
INSERT INTO products_child1 SELECT * FROM products;
Inserting specific columns of a table: We can copy only the columns we wish to insert into a different table from the original table. And for that, we can follow the below syntax:
INSERT INTO first_table(names_of_columns1) SELECT names_of_columns2 FROM second_table;
Here is an example:
INSERT INTO products_child2(product_id,product_name) SELECT product_id,product_name FROM products;
Note: We have not set Passing any value for the Category ID column, so null is assigned by default.
Copying specific rows from a table: We can use a WHERE clause with the SELECT statement to copy specific rows from an existing table and insert them into another one. In this case, we must include the WHERE clause’s conditions to select only relevant data.
INSERT INTO table1 SELECT * FROM table2 WHERE condition;
INSERT INTO products_child3 SELECT * FROM products WHERE product_id > 6;
Insert the top N of rows: We can also copy some TOP N number of records from the other table.
INSERT top(5) INTO products_child4 SELECT * FROM products;
Insert the top percent of rows: We can copy some percent of records from other tables by following the example below.
INSERT top(5) INTO products_child4 SELECT * FROM products;
Conclusion:
In this tutorial, you have been provided with helpful information on how to use the SQL Server INSERT INTO SELECT statement for inserting rows from other tables into a particular table.
