SQL Insert Statement

Now that you have created your table, it's time to put some information into it. You might wonder why this step is important. Why not just use an application to put in the information you want to save? That's a good question, and it's easy to answer.
The reason we want to go through this "manual" step is to show you exactly what applications are doing when you save that information to the database. When the application saves information to the underlying database, it generates the SQL statements and sends them to the database for you.

As an SQL Developer, you are likely familiar with the various commands and statements used to manipulate and manage data within databases. The "INSERT INTO" statement in SQL is specifically designed to streamline the process of adding new data into a database table, making it an essential tool for data management. Here’s how it simplifies the process:

1. Structure and Simplicity: The "INSERT INTO" statement has a straightforward structure. It specifies the table into which the data will be inserted and then defines the values to be added. This clarity and simplicity make the process of adding data easy to understand and implement.
2. Direct Data Entry: The statement allows you to directly insert data into the specified columns of a table. You can insert a single row with multiple columns of data in one command, avoiding the need for multiple transactions or complex procedures.
3. Batch Insertions: SQL also allows for inserting multiple rows of data in a single "INSERT INTO" statement. This feature is particularly useful when you have a large amount of data to be added to the database, as it significantly reduces the number of individual insert commands and speeds up the data entry process.
4. Data Integrity and Accuracy: By specifying the exact columns where data should be inserted, the "INSERT INTO" statement helps maintain the integrity and accuracy of the database. You can ensure that the correct data goes into the correct columns, which is crucial for maintaining the relational structure of the database.
5. Flexibility with Default Values: In cases where your table design includes default values for certain columns, you can omit these columns in your "INSERT INTO" statement. The database will automatically fill these columns with their default values, making data entry more efficient.
6. Compatibility with Other SQL Statements: The "INSERT INTO" statement can be used in conjunction with other SQL statements like "SELECT". This allows for more complex operations like inserting data that is the result of a query from another table, thus providing versatility in data handling.
7. Error Handling: When used correctly, the "INSERT INTO" statement can provide immediate feedback if the insertion fails, for example, due to a violation of the table’s constraints. This immediate feedback allows for quick troubleshooting and correction of data or command syntax.

In summary, the "INSERT INTO" statement in SQL provides a straightforward, efficient, and flexible way of adding new data to a database. Its ability to handle single or multiple data insertions while maintaining data integrity makes it an invaluable tool for SQL developers.

The INSERT INTO statement makes inserting new data into the database very easy. All you need to do is specify into which table you want to insert data, into which columns to insert data, and finally what data to insert. The basic syntax is as follows:

INSERT INTO table_name (column_names) 
VALUES (data_values)

This line of code adds a record to the Category table:

INSERT INTO Category (CategoryId, Category) 
VALUES (1, 'Thriller');

Inserting data is simply a matter of listing each column name (separated by a comma) in the brackets after the table name. In the brackets after the VALUES statement, simply list each item of data to go into the matching column and separate each wih a comma. Character and date data must be wrapped up inside single quotes.

The INSERT statement adds data to a table in the database. This command has several variations.
For the following examples, assume the Persons table was created with the following command:

CREATE TABLE IF NOT EXISTS Persons (
idPerson INT NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(45) NOT NULL DEFAULT '<missing>',
LastName VARCHAR(45) NOT NULL DEFAULT '<none>',
State VARCHAR(10) NULL,
PRIMARY KEY (idPerson)
)

The simplest form of the INSERT statement lists the values to be inserted in the new record after the VALUES keyword. The values must have the correct data types and must be listed in the same order as the fields in the table. The following command inserts a new record in the Persons table:

INSERT INTO Persons VALUES (1, "John", "Doe", "MI")

Some databases will not let you specify values for AUTO INCREMENT fields such as idPerson in this example. If you specify the value null for such a field, the database automatically generates a value for you. If you replace a value with the keyword DEFAULT, the database uses that field's default value if it has one. When it executes the following command, the database automatically generates an idPerson value, the FirstName value defaults to < missing>, the LastName value is set to Markup, and the State value is set to null:

INSERT INTO Persons VALUES (null, DEFAULT, "Markup", null)

The next form of INSERT statement explicitly lists the fields that it will initialize. The values in the VALUES clause must match those listed earlier and they must be in the correct order. Listing the fields that you are going to enter lets you omit some fields or change the order in which they are given. The following statement creates a new Persons record. It explicitly sets the FirstName field to Karl and the State field to Confusion. The database automatically generates a new idPerson value and the LastName value gets its default value <none>:
Ad SQL Database Programming

INSERT INTO Persons (FirstName, State) 
VALUES ("Karl", "Confusion")

The final version of INSERT INTO described here gets the values that it will insert from a SELECT statement (described in the next section) that pulls values from a table.
The next lesson is an introduction to the INSERT statement, which is used to add information directly to the database.