When working with different databases that use SQL for programming, it is often assumed that code from one database can easily be used in another. However, the reality is quite different. Not all scripts from one database will work in another. In this article, we will explore a question about inserting multiple rows in a single SQL query and provide a solution that works across multiple databases.

The Question

A user asked the following question: “Write a SQL code which will insert multiple rows in a single SQL query. The condition for this script is that it should work in SQL Server, MySQL, and PostgreSQL without changing a single line of code.”

The Answer

Writing a code to insert multiple rows in a single SQL query is not a difficult task. However, creating a script that works with multiple databases can be a challenge. Nevertheless, it is possible to create a script that will work across different databases.

Let’s start by creating a sample table with two columns:

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

Next, we can insert three different rows in a single SQL query:

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

To verify the inserted rows, we can run the following query:

SELECT *
FROM SampleTable;

Finally, we can drop the sample table:

DROP TABLE SampleTable;

Now, let’s run this query in multiple databases and see the outcome:

SQL Server

The query runs successfully in SQL Server.

MySQL

The query also runs successfully in MySQL.

PostgreSQL

Similarly, the query runs successfully in PostgreSQL.

As you can see, the script works across different databases without any modifications.

Alternatively, you can also build the SQL script using the UNION operator. However, the solution demonstrated in this query is the recommended approach.

Thank you for reading this article. We hope you found it helpful in understanding how to insert multiple rows in a single SQL query that works across different databases.