MySQL Insert Command vs T-SQL Query Syntax with Examples

Developers are lifelong students. Gaining new knowledge as fast as we can is always our gig. If you’re coming from the T-SQL camp, what’s the faster way to learn MySQL? Often you want to compare syntaxes. Last time, you learned about creating a table in MySQL. Our today’s post will take you a step further. We’ll insert records with MySQL INSERT.

How easy can it be? Both database platforms use SQL, but we know that both these database products have their few additions to the standard SQL syntax. MySQL INSERT is no exception.

Thus, if you are you tired of comparing references for MySQL and SQL Server, today is your lucky day. I have curated the syntax and did the comparison for you. Also, we will have examples to check out. Finally, we’ll have another free cheat sheet.

Now, before we dive in, you can also get a FREE PDF cheat sheet about this article by filling-up the opt-in form below.

[sendpulse-form id=”12097″]

Ready? Great!

MySQL INSERT INTO Query Syntax (Table Values Constructors)

But before we dive into the details, let’s make a few things clear:

• The MySQL version I’m using here is 8.0.23, with the InnoDB storage engine.
• The SQL Server version is 2019.

First, let’s try to insert one record into a table. The basic syntax is similar to that for SQL Server. However, it is if you don’t include the database and schema name and don’t enclose the column names with backticks.

CREATE TABLE testdatabase.people
(`ID` int NOT NULL PRIMARY KEY,
 `Lastname` varchar(50) NOT NULL,
 `FirstName` varchar(50) NOT NULL,
 `MiddleName` varchar(50) NULL DEFAULT '',
 `Rank` varchar(20) NOT NULL,
 `Ship` varchar(50) NOT NULL,
 `ShipRegistry` varchar(20) NOT NULL,
 `ModifiedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP());
  
INSERT INTO people
(`ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, `Ship`, `ShipRegistry`)
VALUES (3,'Archer','Jonathan','','Captain','Enterprise','NX-01');

Specifying the actual location of the table will be slightly different. As I have indicated in this post, a database is synonymous with a schema in MySQL. Check the modified sample below:

INSERT INTO testdatabase.people
(`ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, `Ship`, `ShipRegistry`)
VALUES (3,'Archer','Jonathan','','Captain','Enterprise','NX-01');

Have you noticed the absence of the schema name above?

Meanwhile, the VALUES clause above forms a table value constructor. Likewise, SQL Server has that feature too.

Insert Multiple Rows into Tables in MySQL

Let’s try inserting multiple rows.

INSERT INTO testdatabase.people
(ID, LastName, FirstName, MiddleName, Rank, Ship, ShipRegistry)
VALUES (4,'Janeway','Kathryn','', Captain','USS Voyager', 'NCC-74656'), 
       (5, 'Sisko','Benjamin','','Captain','USS Defiant','NX-74205');

The above code will insert two records using two table value constructors. You can also use expressions instead of literal values. The example below uses a subquery for each column:

CREATE TABLE testdatabase.people2 LIKE people;

INSERT INTO testdatabase.people2
(`ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, `Ship`, `ShipRegistry`)
VALUES (4,'Janeway','Kathryn','','Captain','USS Voyager', 'NCC-74656'), 
       (5, (SELECT Lastname FROM people WHERE ID=5), 
           (SELECT Firstname FROM people WHERE ID=5), 
           (SELECT MiddleName FROM people WHERE ID=5),
           (SELECT `Rank` FROM people WHERE ID=5),
           (SELECT Ship FROM people WHERE ID=5),
           (SELECT ShipRegistry FROM people WHERE ID=5));

The subqueries look redundant in the query above. But this will generate an error:

INSERT INTO testdatabase.people2
(`ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, `Ship`, `ShipRegistry`)
VALUES (4,'Janeway','Kathryn','','Captain','USS Voyager', 'NCC-74656'), 
       (SELECT `ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, 
        `Ship`, `ShipRegistry` FROM people);    -- ERROR!

The table value constructor expects a literal or an expression for each field in the column list. Moreover, the subqueries in the previous example return a single row with one value, and that’s valid. However, the SELECT statement above will trigger an error because it returns multiple rows and columns.

Another option is to specify the ROW keyword like in the example below:

INSERT INTO testdatabase.people
(ID, LastName, FirstName, MiddleName, Rank, Ship, ShipRegistry)
VALUES ROW(4,'Janeway','Kathryn','', Captain','USS Voyager', 'NCC-74656'), 
       ROW(5, 'Sisko','Benjamin','','Captain','USS Defiant','NX-74205');

But the ROW keyword in the INSERT statement is not supported in SQL Server.

MySQL INSERT INTO with SELECT from Another Table

You can add records into a table using the SELECT statement:

INSERT INTO people2
(ID, Lastname, FirstName, MiddleName, `Rank`, Ship, ShipRegistry)
SELECT ID, Lastname, FirstName, MiddleName, `Rank`, Ship, ShipRegistry FROM people;

As you can see, it’s the same with T-SQL without the database name, schema name, and the backtick character. Rank is a reserved word, by the way.

Hence, the backtick characters enclosing the word Rank is a must.

You can express it without the column list. Simply specify the column names in the same sequence as the target table’s column arrangement.

TRUNCATE TABLE testdatabase.people2;

INSERT INTO testdatabase.people2
SELECT ID, Lastname, FirstName, MiddleName, `Rank`, Ship, ShipRegistry, ModifiedDate 
FROM people;

SELECT * FROM testdatabase.people2

See the output below from dbForge Studio for MySQL:

Sounds good? Let’s expand more on the topic of handling column values.

INSERT and Tables Column Values

This section will dig deeper into column values when using MySQL INSERT. There are four of them:

• Unicode values.
• Default values.
• Using autoincrement.
• Values from the user-defined variables.

Insert Unicode String

In T-SQL, you’re familiar with preceding a string value with N. When using that, the SQL Server assumes that the value is a Unicode value. In MySQL, you don’t need that.

Check out the example below:

CREATE TABLE IF NOT EXISTS UnicodeNames
(ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
 FullName varchar(50) NOT NULL);

INSERT INTO UnicodeNames
(FullName)
VALUES ('김지수'),('김제니'),('박채영'),('ลลิษา มโนบาล');

SELECT * FROM UnicodeNames

There a few points to take note of:

1. Using NVARCHAR in creating a table in MySQL 8 will be automatically changed to VARCHAR.
2. There is no need to precede the Unicode value with N, like N’김지수’.
3. You need to edit the above code a little to make it run successfully in SQL Server.

Check the result below:

Here’s the equivalent code in T-SQL:

CREATE TABLE UnicodeNames
(ID int NOT NULL IDENTITY PRIMARY KEY,
 FullName nvarchar(50) NOT NULL);

INSERT INTO UnicodeNames
(FullName)
VALUES (N'김지수'),(N'김제니'),(N'박채영'),(N'ลลิษา มโนบาล');

SELECT * FROM UnicodeNames

Try removing the ‘N’ from one of the Unicode values above. The code will run fine, but you check the output below in SQL Server Management Studio:

How to Insert Default Value in MySQL

The column default values act when you insert a record without specifying a value. We used that in the first example above. Here’s the column definition again:

`ModifiedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP()

The column ModifiedDate defaults to the current date and time on the server. That’s why when we did this:

INSERT INTO testdatabase.people
(`ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, `Ship`, `ShipRegistry`)
VALUES (3,'Archer','Jonathan','','Captain','Enterprise','NX-01');

The result is this:

Very neat and saving on keystrokes. This same behavior also happens in SQL Server.

Meanwhile, if you want to make it explicit, just specify the column name with a value of DEFAULT:

INSERT INTO testdatabase.people
(`ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, `Ship`, `ShipRegistry`, `ModifiedDate`)
VALUES (6, 'Pyke','Christopher','','Captain','USS Enterprise','NCC 1701', DEFAULT);

It also works in SQL Server with a slight modification.

There is another time and keystroke saver. It is having an autoincrement column value.

INSERT into AUTO_INCREMENT Column

Instead of getting the last integer value on a table and adding 1, it’s better to define an autoincrement column. it is good for primary keys. We did that in the Unicode table example.

Here’s a portion of it again:

CREATE TABLE IF NOT EXISTS UnicodeNames
(ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
 FullName varchar(50) NOT NULL);

Meanwhile, in SQL Server, we use the keyword IDENTITY instead of AUTO_INCREMENT.

Figure 2 reveals the result of defining an autoincrement column. The integer values were generated without specifying it in the INSERT statement.

Using User-defined Variables to Set Column Values

Another common case for inserting records in T-SQL is applying user-defined variables to set column values. It is helpful for making ad-hoc scripts and stored procedures.

But first, defining variables in MySQL can be done using SET. Unlike in SQL Server, you use DECLARE. Have a look at the example:

SET @stringValue = 'string value';
SET @numericValue = 1287;
SET @booleanValue = TRUE;
SET @dateValue = CURRENT_DATE();

CREATE TABLE IF NOT EXISTS TableDataTypeValues
(ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
 stringValue varchar(50) NOT NULL,
 numericValue int NOT NULL,
 BooleanValue bit not NULL DEFAULT FALSE,
 dateValue date NULL);

INSERT INTO TableDataTypeValues
(stringValue, numericValue, BooleanValue, dateValue)
VALUES (@stringValue, @numericValue, @booleanValue, @dateValue);

As you can see above, the variables act as expressions to fill the column values in the VALUES clause.

Insert into Temporary Table in MySQL

Here, we review using MySQL INSERT in temporary tables. T-SQL developers are familiar with the ‘#‘ symbol preceding a temporary table name. In MySQL, temporary tables are created using CREATE TEMPORARY TABLE table_name. It does not have the ‘#‘ symbol. Hence, among the lines of your script, a temporary table name may look the same as regular table names:

CREATE TEMPORARY TABLE tmpkpopgroup
(ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
 GroupName varchar(50) NOT NULL,
 ModifiedDate datetime DEFAULT CURRENT_TIMESTAMP());

INSERT INTO tmpkpopgroup
(GroupName)
VALUES ('BTS'),('SEVENTEEN'),('TWICE'),('MOMOLAND'),('GIRLS GENERATION'),('BLACKPINK'),('OH MY GIRL'),('RED VELVET');

SELECT * FROM tmpkpopgroup
ORDER BY GroupName;

In other words, inserting records to temporary tables looks the same as regular tables.

Here’s the result:

MySQL INSERT…ON DUPLICATE KEY UPDATE – Unsupported Syntax in T-SQL

Finally, MySQL INSERT has syntax cases unsupported in T-SQL. One of them is INSERT…ON DUPLICATE KEY UPDATE. What can it do?

When a table has a unique key and you happen to insert a value that will cause a duplicate, the INSERT will fail. However, when you use INSERT…ON DUPLICATE KEY UPDATE, an update of the existing row will occur instead. The error won’t occur.

Let’s say we have these records in the people table:

Notice the value of the ship registry boxed in red, and that entire line. The thing is, we are going to insert the same record with ON DUPLICATE KEY UPDATE.

Now, let’s fire the statement below:

INSERT INTO testdatabase.people
(`ID`, `LastName`, `FirstName`, `MiddleName`, `Rank`, `Ship`, `ShipRegistry`, `ModifiedDate`)
VALUES (5, 'Sisko','Benjamin','','Captain','USS Defiant','NCC-75633', DEFAULT)
ON DUPLICATE KEY UPDATE ShipRegistry = 'NCC-75633';

SELECT * FROM testdatabase.people;

Is there an error? Let’s check it!

Cool, isn’t it?

The same effect happens when you do this:

UPDATE people 
SET ShipRegistry = 'NCC-75633'
WHERE ID = 5

Meanwhile, you can express the intention in T-SQL like this:

IF EXISTS(SELECT id FROM people WHERE id = 5)
	UPDATE people 
	SET ShipRegistry = 'NCC-75633'
	WHERE ID = 5
ELSE
	INSERT INTO people
	(ID, LastName, FirstName, MiddleName, [Rank], Ship, ShipRegistry, ModifiedDate)
	VALUES (5, 'Sisko','Benjamin','','Captain','USS Defiant','NCC-75633', DEFAULT);

The same result will occur.

Conclusion

Benjamin Franklin once said, “An investment in knowledge always pays the best interest.” I hope this article will pay off for you in time.

Let’s have a recap:

1. The basic MySQL INSERT syntax is almost the same in T-SQL except for the absence of the schema name and the use of backticks.
2. Inserting records in MySQL from another table is almost the same as in T-SQL.
3. Column values are handled differently in MySQL INSERT. Unicode values are the most notable. Also, the syntax is different in the use of AUTO_INCREMENT.
4. Inserting records in a temporary table in MySQL has a minor difference in T-SQL with the ‘#‘ symbol.
5. INSERT…ON DUPLICATE KEY UPDATE looks good on shortening the syntax to avoid duplicate errors.

I hope the article proved useful to you while you are learning MySQL. If you like this post, please share it on your favorite social media platforms!

Tags: mysql, t-sql Last modified: June 27, 2023