SQL, or Structured Query Language, is a language used in relational databases.
There are many different commands to learn in order to navigate SQL efficiently.
This guide focuses on the INSERT INTO SQL command. This particular SQL feature is all about how to insert data in SQL into a database.
INSERT INTO command is used to add new rows, columns, and data into a table on a relational database.
We will look at the syntax, examples, and even compare other SQL commands for a wider perspective.
The SQL ‘INSERT INTO’ Statement
The SQL ‘INSERT INTO’ statement is a specific SQL command.
It is used to insert a new piece of data into the relational database. This is normally in the form of table records. It can also be into specific columns.
The first thing to get your head around is that you will need to know the ‘INSERT INTO’ command if you are going to insert any data in Structured Query Language.
‘INSERT INTO’ – The Syntax
The syntax, which describes phrase structure, for the INSERT INTO statement is the next step to learn and make sure you have an understanding of.
The syntax looks like this:
Part One: INSERT INTO table name (column 1, column 2, column 3)
Part Two: VALUES (value1, value 2, value 3)
Try to avoid any syntax error, i.e., spelling mistakes or wrong words, as this will yield a null result.
A syntax error includes any command that is not actually a part of the SQL database. Therefore, the table definition will not be changed.
Explanation: The INSERT INTO Statement
So, INSERT INTO, is your primary SQL command.
You then have lists of values defined by your INSERT statement or INSERT query, whichever you wish to call it.
The first value is the table name. This could be an employee’s table or a customer’s table, or even a temporary table for floating data.
Whatever your data, the SQL INSERT command remains the same.
Your target columns have names or labels, and this is where you insert them. By inputting the column names, you create a column list. So far, so simple.
Take note that this is a comma-separated list.
The commas are an integral part of the SQL command. They separate each individual value to conform to clarity standards. That way the data doesn’t get muddled up or invalidated.
Next are the values. Values are further definitions in the actual table. They are the pieces of data, or the type of insert, that are new values, or changed values, in the columns.
This is the SQL command for inserting data into your target table. You will need to know the source table and the column names for proper execution.
You may be dealing with a single statement or a compound statement.
Insert Data Only In Specified Columns
Sometimes the need arises to insert data into a specific column. If that’s the case, the syntax will look different from inserting data into a table.
Columns are the different rows (columns) of the table. They contain the headers, or descriptions, of the values beneath them.
Each column describes what individual values it holds. This can be a range of things from word inputs, number inputs, and even short phrases.
They are an integral part of relational databases but don’t worry. Chopping and changing them is simple thanks to SQL.
The syntax for inserting data only in specified columns is as follows:
The Command: INSERT INTO table name (column1, VALUE1)
You may need this command if only one specific value has changed, or needs updating. For example, a change of address or name.
Example: INSERT INTO Patients (PetName, Greg)
‘INSERT INTO’: Example Time
To build on what we have already learned, let’s take a look at an example with actual values.
Our example will be for the patients at a veterinarian practice.
This is from a fictional sample database.
SQL command: INSERT INTO Patients (PetName, OwnerName, Address, ZipCode, Country,) VALUES (Padfoot, Mrs. Jones, 134 Avenue, 98238, Conway,)
Table Name: Patients
Column List: PetName, OwnerName, Address, ZipCode, Country
Value Inputs: Padfoot, Mrs. Jones, 134 Avenue, 98238, Conway
So our table now contains the name, owner’s name, address, zip code, and country of the pet/patient registered at the surgery.
Other Common SQL Commands
Alongside the INSERT INTO statement, there are a few other SQL commands that may help you with your database creations and manipulations.
The most common are as follows:
–CREATE statement: The create command is used to create a column or a table.
–SELECT statement: The SELECT statement is used to select certain pieces of data within the table or column.
–INSERT statement: INSERT statement is used to insert new data or update existing data within the table, or column.
–DELETE statement: DELETE statement is used to delete data pieces from the table or columns.
These are all held under different branches of SQL. DML, DQL, DDL, DCL, and TCL.
DML: Data Manipulation Language. This is used to manipulate or change the data.
DQL: Data Query Language. This is used to ask questions about the data or perform queries.
DDL: Data Definition Language. This is used to describe data.
DCL: Data Control Language. This controls who can access the table data.
TCL: Transaction Control Language. This controls the transactions within the database.
Structured Query Language is the language used to control, manipulate, insert, select, create and delete data from and in relational databases.
Relational databases hold information that relates to inserts in the tables and columns. For example, names, correlating addresses, ages, that sort of thing.
This guide has focused on the SQL INSERT INTO command.
We’ve explored how to write the insert command in proper syntax, how it is applied into a table, and how it interacts with the data entered into a relational database.
The main things to remember are:
- Always spell everything correctly.
- Do not forget your commas – commas are important!
- Label everything correctly. Values, Tables, Columns. They are all different parts of the database, and therefore need separating.
- You can change a whole column or an individual one.