Insert Into Sql - sql - sql tutorial - learn sql
- The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
- The INSERT INTO statement is used to add new records into a database table.
- In SQL, there are basically two ways to INSERT data into a table: One way is to insert it one row at a time, the other is to insert multiple rows at a time.
Syntax:
- The syntax for inserting data into a table one row at a time is as follows:
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);
Read Also
Inline view in sql.Examples:
- The examples refer to a table that has the following structure,
Table product_Information
| Column Name | Data Type |
|---|---|
| Store_Name | char(50) |
| Manager_ID | integer |
| Sales | float |
| Txn_Date | datetime |
Example 1: All column names are specified
- We want to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999.
- That day, this store had $900 in sales, and the Manager_ID for this store is 10. We will use the following SQL script:
INSERT INTO product_Information (Store_Name, Manager_ID, Sales, Txn_Date)
VALUES ('Los Angeles', 10, 900, 'Jan-10-1999');
- Now the table will hold the following data:
Table product_Information
| Store_Name | Manager_ID | Sales | Txn_Date |
|---|---|---|---|
| Los Angeles | 10 | 900 | Jan-10-1999 |
- Please note that we can specify the column names in any order -- the order does not have to be the same as that of the table.
- For example, the following SQL statement is equivalent to the SQL statement above:
INSERT INTO product_Information (Sales, Store_Name, Manager_ID, Txn_Date)
VALUES (900, 'Los Angeles', 10, 'Jan-10-1999');
Example 2: None of the column names are specified
- If we leave off the column names in the INSERT INTO statement, we will need to make sure that data is inserted in the same column order as that in the table. For example,
INSERT INTO product_Information
VALUES ('Los Angeles', 10, 900, 'Jan-10-1999');
- It will give us the desired result, while
INSERT INTO product_Information
VALUES (900, 'Los Angeles', 10, 'Jan-10-1999');
- It will result in product_Name being set to 900, Manager_ID being set to 'Los Angeles', and Sales being set to 10.
Read Also
Decode function in sql.Example 3: Some of the column names are specified
- In the first two examples, we insert a value for every column in the table.
- Sometimes, we may decide to insert value into some of the columns and leave the rest of the columns blank.
- For those cases, we simply specify the column names that we want to insert values into in our SQL statement.
Below is an example:
INSERT INTO product_Information (Store_Name, Sales, Txn_Date)
VALUES ('New York', 500, 'Jan-10-1999');
- Now the table becomes:
Table product_Information
| Store_Name | Manager_ID | Sales | Txn_Date |
|---|---|---|---|
| Los Angeles | 10 | 900 | Jan-10-1999 |
| New York | 500 | Jan-10-1999 |
- In this case, the value for the Manager_ID column in the second row is NULL. NULL means that data does not exist.
Exercises
- Let's assume we start with the Store_Information table shown above. What does the table look like after the following SQL statement is executed?
INSERT INTO Store_Information VALUES ('San Jose',25,700,'Jan-10-1999');
- Answers: The table becomes,
| Store_Name | Manager_ID | Sales | Txn_Date |
|---|---|---|---|
| Los Angeles | 10 | 900 | Jan-10-1999 |
| New York | 500 | Jan-10-1999 | |
| San Jose | 25 | 700 | Jan-10-1999 |
- Continuing with Question 1. What does the table look like after the following SQL statement is executed?
INSERT INTO Store_Information (Manager_ID,Txn_Date,Sales) VALUES (10,600,'Jan-11-1999');
- Answers: The table becomes,
| Store_Name | Manager_ID | Sales | Txn_Date |
|---|---|---|---|
| Los Angeles | 10 | 900 | Jan-10-1999 |
| New York | 500 | Jan-10-1999 | |
| San Jose | 25 | 700 | Jan-10-1999 |
| 10 | 600 | Jan-11-1999 |
- Using the same table as above. What does the table look like after the following SQL statement is executed?
INSERT INTO Store_Information ('Portland',30,650,'Jan-11-1999');
- Answers: The table becomes,
| Store_Name | Manager_ID | Sales | Txn_Date |
|---|---|---|---|
| Los Angeles | 10 | 900 | Jan-10-1999 |
| New York | 500 | Jan-10-1999 | |
| San Jose | 25 | 700 | Jan-10-1999 |
| 10 | 600 | Jan-11-1999 |
SQL Insert Data Sample Application 1
SQL Insert Data Sample Application 2
