How to add static value while INSERT INTO with SELECT in a MySQL query?

You can add static value when you use INSERT INTO SELECT MySQL query. Write the value directly in the select statement or you can add with the help of variable which initializes the value.

Case 1 − Place the value directly in the INSERT INTO SELECT statement. The syntax is as follows −

INSERT INTO yourSecondTableName(yourColumnName1,yourColumnName2,....N)
   SELECT yourColumnName1 ,yourColumnName2,.....N,yourStaticValue from yourFirstTableName;

Case 2 − Add using variable. The syntax is as follows −

SET @yourVariableName − = yourstaticValue;
   INSERT INTO yourSecondTableName(yourColumnName1,yourColumnName2,....N)
   SELECT yourColumnName1 ,yourColumnName2,.....N,@yourVariableName from yourFirstTableName;

To understand the above syntax, you need to create two tables. The first table will have some records in the table while second table will insert the first table record using insert into select statement and it will add a static value.

Let us create the first table. The query to create a table is as follows −

mysql> create table First_table
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> FirstValue int,
   -> SecondValue int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.56 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into First_table(FirstValue,SecondValue) values(10,14);
Query OK, 1 row affected (0.20 sec)

mysql> insert into First_table(FirstValue,SecondValue) values(12,13);
Query OK, 1 row affected (0.52 sec)

mysql> insert into First_table(FirstValue,SecondValue) values(100,110);
Query OK, 1 row affected (0.14 sec)

mysql> insert into First_table(FirstValue,SecondValue) values(45,55);
Query OK, 1 row affected (0.27 sec)

Display all the records from the table using select statement. The query is as follows −

mysql> select *from First_table;

The following is the output −

+----+------------+-------------+
| Id | FirstValue | SecondValue |
+----+------------+-------------+
|  1 |         10 |          14 |
|  2 |         12 |          13 |
|  3 |        100 |         110 |
|  4 |         45 |          55 |
+----+------------+-------------+
4 rows in set (0.00 sec)

Let us create second table. The query to create a table is as follows −

mysql> create table Second_table
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Value1 int,
   -> Value2 int,
   -> Value3 int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.81 sec)

In the above table there are three columns in which two columns will get the value from the first table while third column will get the value directly or indirectly.

The query INSERT INTO SELECT statement is as follows −

Case 1 − Add the static value to the third column. The query is as follows −

mysql> insert into Second_table(Value1,Value2,Value3)
   -> select FirstValue,SecondValue,45 from First_table;
Query OK, 4 rows affected (0.16 sec)
Records − 4 Duplicates − 0 Warnings − 0

Here, I have added 45 to the third column. Now check all records from the table using select statement. The query is as follows −

mysql> select *from Second_table;

The following is the output −

+----+--------+--------+--------+
| Id | Value1 | Value2 | Value3 |
+----+--------+--------+--------+
|  1 |     10 |     14 | 45     |
|  2 |     12 |     13 | 45     |
|  3 |    100 |    110 | 45     |
|  4 |     45 |     55 | 45     |
+----+--------+--------+--------+
4 rows in set (0.00 sec)

Case 2 − When you use a variable. The query is as follows −

mysql> set @staticValue − = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into Second_table(Value1,Value2,Value3)
   -> select FirstValue,SecondValue,@staticValue from First_table;
Query OK, 4 rows affected (0.19 sec)
Records − 4 Duplicates − 0 Warnings − 0

Adding a value 20 for my third column. Let us check the records of the table once again using select statement. The query is as follows −

mysql> select *from Second_table;

The following is the output −

+----+--------+--------+--------+
| Id | Value1 | Value2 | Value3 |
+----+--------+--------+--------+
|  1 |     10 |     14 |     20 |
|  2 |     12 |     13 |     20 |
|  3 |    100 |    110 |     20 |
|  4 |     45 |     55 |     20 |
+----+--------+--------+--------+
4 rows in set (0.00 sec)