In SQL, data insertion is done using the sql INSERT INTO command. Indeed, the insert into command allows you to add a new record in the table of our database (whether the engine used is MariaDB, Mysql, PostGreSQL, Transact-SQL, Oracle or any other DBMS).
In SQL, INSERT INTO is the most commonly used command in SQL to manipulate our data as well as the other three fundamental commands in SQL, namely SELECT,UPDATE and DELETE.
INSERT INTO will therefore be used to create one or more new entries. We will also discuss the BULK INSERT method and the LOAD DATA INFILE command, which allows the massive addition of data from a CSV or TXT file simply and quickly.
- Syntax of the INSERT INTO Command
- SQL INSERT INTO command with SELECT in SQL
- INSERT INTO with multiple entries
- INSERT IGNORE INTO add an entry or do nothing if the record already exists
- REPLACE INTO creation of a record or update if it already exists
- INSERT BULK in SQL
- INSERT LOAD DATA INFILE
- Example INSERT INTO query in PHP with MySQL
- Example INSERT INTO query in C# with MySQL
0-INSERT INTO query example in C# with SQL
Basic syntax of the SQL INSERT INTO command
To insert a new record in the database, the INSERT INTO command is used as follows:
INSERT INTO table (column_1, column_2, column_3) VALUES ('value_1', 'value_2', 'value_3')
We specify the INSERT INTO command followed by the name of each of the columns, then we designate each value that will be assigned to it encapsulated by quotation marks (or apostrophes). This syntax has the advantage of not requiring us to fill in each of the columns. Indeed we can perfectly perform the following SQL INSERT command:
INSERT INTO table (column_1, column_2) VALUES('value_1', 'value_2')
Assuming column_1 is a self-incrementing primary key, this saves us from writing:
INSERT INTO table (column_1,column_2, column_3) VALUES (NULL,'value_2', 'value_3')
The value of our first column will be automatically assigned by the database.
It is also possible to perform an SQL INSERT INTO command without specifying the columns that we want to use in this case, the DBMS engine will implicitly interpret the order of the columns of the table. We can also include functions native to the SQL engine directly in our query. Here we will use the NOW() function which returns the date and time when executing the SQL query.
For a table called comments with four columns:
user comment_date comment, the INSERT query specific to this format will be:
INSERT INTO comments VALUES ('guest', NOW(),'Good job')
Which would be equivalent to the Sql INSERT INTO query:
INSERT INTO comments (user,comment_date,comment) VALUES ('guest', NOW(),'Good job')
Advanced syntax with SELECT of the INSERT INTO command
The database engine allows us to perform an SQL INSERT INTO command directly from a SELECT query.
It is then possible to copy the content of a specific row from one SQL table to another.
INSERT INTO table_1 SELECT column_1,column_2,column_3 FROM table_2 WHERE condition
INSERT INTO also allows you to perform insertions by sub-query. Although this kind of command is decried for a question of performance, they can prove to be useful in certain singular cases.
The syntax of these queries will follow this pattern:
INSERT INTO table_1 (column_1,column_2, column_3)
VALUES ((SELECT column_1 FROM table_2 WHERE condition LIMIT 1),'value_2', 'value_3')
Note that it is imperative that the subquery:
(SELECT column_1 FROM table_2 WHERE conditions LIMIT 1)
Returns only one and unique result from a single column. Otherwise the SQL engine will return an error and the record will not be included in the table during the INSERT INTO command.
Syntaxe avancée de la commande SQL insert into avec multiples entrées
It is possible to create multiple insertions of entries in our database, by writing only once the header of the INSERT INTO command and by adding several batches of VALUES to it, all separated by a single comma. Assuming again our three-column users table, the syntax of our advanced SQL query therefore becomes:
INSERT INTO comments (user,comment_date) VALUES ('Marc',NOW()),
('John',NOW()),('Albert',NOW()),('Maria',NOW()),('Sandra',NOW()) ;
This INSERT INTO query will produce five records in our comments table. Note that it is quite possible to take advantage of the implicit order of the columns of the table and therefore not to specify them. This will force us to assign a value to each column, otherwise the query will cause a DBMS error.
Similarly, we can also apply what we have seen with inserts via subquery (although this is still questionable from a performance perspective).
Advanced SQL INSERT INTO command syntax with CAST
To ensure the proper functioning of the sql insert into query, it is sometimes necessary to perform data conversions. In SQL, this action is performed using the SQL CAST command. It is thus possible to convert a character string into a date, or into an integer, or even an integer into a character string.
To perform a typing conversion from one format to another, simply use the CAST function, assign it the data we want to convert as a parameter, followed by AS the expected format (DATETIME,DATE,VARCHAR, SIGNED INTEGER). As the following example demonstrates the correct use of the SQL insert into query with CAST.
INSER INTO comments (user,comment_date) VALUES ('Maria',CAST('2023-01-13 14:30:23' AS DATETIME))
How to copy a table to another with the SQL INSERT INTO command?
INSERT INTO table_2 (column_1, column_2, column_3)
SELECT column_1, column_2, column_3
FROM table_1
WHERE condition;
How to add non-existent data but do nothing if this record is already stored in the table with INSERT IGNORE INTO?
The INSERT IGNORE INTO command behaves like INSERT INTO except that it does not add the record if a record with the same primary key already exists.
The syntax of this command is very close to a classic INSERT INTO. It is imperative that the records in the table be subject to a primary key, or a uniqueness constraint.
INSERT IGNORE INTO table_1 (column_1_PK,column_2,column_3) VALUES(value_1_pk,value_2,value_3)
How do I update or insert a record using the Sql REPLACE INTO command?
The SQL REPLACE INTO command is used to update an entry, or to insert it if it does not exist.
This command has several syntaxes, but the simplest (and above all the most logical given its application) remains the syntax which approaches the UPDATE command.
Advanced SQL syntax with BULK INSERT command IN T-SQL
REPLACE INTO table_1
SET
column_1_PK=value_1_pk,
column_2=value_2
To load batches of records from large csv or txt files in the most optimized way possible.
Some SQL database management engines (including Transact-SQL, ORACLE,SQL), add their command, BULK, to the list.
A BULK query aims to load rowsets directly from a dataset (also called dataset) without the need to create each query manually.
The syntax of a BULK query is quick and simple:
BULK INSERT my_table FROM 'c:\my_file.csv' ;
Advanced SQL syntax with the LOAD DATA INFILE command
Other languages such as MySQL and MariaDB have responded by adding the LOAD DATA INFILE command, it behaves in the same way as the BULK query seen above.
LOAD DATA INFILE '/my_file.csv/' INTO TABLE my_table ;
Many parameters exist, but the two most important are FIELDS TERMINATED BY which allows you to define that each field is separated by a semicolon.
LOAD DATA INFILE '/my_file.csv/' INTO TABLE my_table FIELDS TERMINATED BY ' ;' ;
And the IGNORE X LINES parameter which is used to declare that the CSV file has a header and that it must be ignored by the command when processing the batch of data.
LOAD DATA INFILE '/my_file.csv/' INTO TABLE my_table FIELDS TERMINATED BY ' ;' IGNORE 1 LINES ;
INSERT INTO in MySQL with PDO object using PHP
To connect to a MySQL database in PHP we will use the PDO object.
The PDO object is a PHP class that allows you to create an interface with the database, here MySQL.
This object simplifies access to the database and adds an additional layer of security when handling data.
In the following example we will perform the SQL INSERT INTO command in our user table (which still contains its three columns user, log, comment).
All while using the PDO object and prepared statements to protect against possible SQL injections.
We assign to the field user the value "john" and to the field comment the value "Comment on the use", the field log will be filled automatically by the value which will be returned by the function NOW() this function returning the date and the time when the query is executed.
To do this we use the tags named :user as well as the tag named :comment in our SQL query. When preparing the request via the prepare() method, these will be replaced by the escaped and safe values.
To perform an INSERT INTO MYSQL query in C#, you must first retrieve the DLL (MySql.Data) and add it as a reference to our project. Once it's done, we will have to open the connection to our database, carry out our command, this is done in a few lines of code:
using MySql.Data.MySqlClient;
namespace TestMysql{
class Program{
static void Main(string[] args)
{
String strConnection = @"SERVER=127.0.0.1;DATABASE=my_database;PORT=3306;
UID=my_username;PASSWORD=my_password;"
MySqlConnection Dba = new MySqlConnection(strConnection);
Dba.Open();
MySqlCommand Cmd = new MySqlCommand("INSER INTO comments VALUES('Marc',NOW(),'Good job')", Dba,null);
Cmd.CommandTimeout = 300;
Cmd.ExecuteNonQuery();
Dba.Close();
}
}
}
Note that no system allows you to prepare and therefore secure your request as is the case in PHP, so you will have to take care of it before executing the SQL request.
INSERT INTO in SQL using C#
To perform an INSERT INTO SQL query in C#, the approach is very similar to that for MySQL. We define a connection string, then we open the connection with the SQL database. We write the query, we execute it, we close the connection to the database.
using System.Data;
using System.Data.SqlClient;
namespace TestCsharp{
class Program{
static void Main(string[] args)
{
String strConnection = @"Data Source=127.0.0.1\WEBSQL;
Initial Catalog=my_database;Persist Security Info=True;
User ID=my_user;Password=my_password"
SqlConnection Dba = new SqlConnection(strConnection);
Dba.Open();
SqlCommandCmd = new SqlCommand("INSER INTO comments VALUES('Marc',NOW(),'Good job')",
Dba,null);
Cmd.CommandTimeout = 300;
Cmd.ExecuteNonQuery();
Dba.Close();
}
}
}
Now you now how to use insert queries in SQL in both C# and PHP. We now urge you to be very careful when implementing for public applications, because this publication does not deal with the security aspects of INSERTs in SQL.
EN 
Español - ES 
Français - FR 
