How to Insert Multiple Rows in a Single SQL Query – Interview Question of the Week #069

There are so many different databases in the world and many of them use the language SQL for their programming. It is very easy to assume that if they are using SQL, we can easily use code from one database to another database. Unfortunately the reality is very different, not all the scripts from one database works in another database. Today we will see very interesting question where the user asked a question about inserting multiple rows in a single SQL query.

Question: Write a SQL code which will insert multiple rows in a single SQL query. The condition for this script was such that it should work in SQL Server, MySQL and PostgreSQL without changing a single line code.

Answer: Writing a code to insert multiple rows in a single SQL query is not a difficult task, but it is indeed a challenge to create a script which will work with multiple database. However, it is possible to create a script which will work across multiple databases.

First, let us create a sample table with two columns in it. Once the table is created insert three different rows in a single SQL query.

CREATE TABLE SampleTable (ID INT, Col1 VARCHAR(100));

INSERT INTO SampleTable (ID, Col1)
VALUES (1, 'One'), (2, 'Two'), (3, 'Three');

SELECT *
FROM SampleTable;

DROP TABLE SampleTable;

Now let us run this query in multiple databases and see the outcome.

SQL Server

MySQL

PostgreSQL

That’s it. This was indeed a very interesting question. Just so you know alternatively you can also build the SQL Script with the help of the UNION. However, the solution which demonstrated in this query is the new way to go.

Reference: Pinal Dave (https://blog.sqlauthority.com)