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)
10 Comments. Leave new
You should also mention that multiple values is available only for SQL Server version of 2008+ and that the values statement is limited to 1000 rows.
for sql server, I also like
insert into table1
(col1, col2, col3)
(select col1, col2, col3 where whatever = this)
Interesting. I’ve been using this for years with Microsoft SQL Server, but I didn’t realize that the same multi-row syntax works with MySQL and PostgreSQL as well.
Thanks!
Hi Pinal, it’s not related to the above post. I have another issue, importing .xlsx files using OPENROWSET is very slow. my excel file can contain up to 700,000 records. currently I tried with 400,000 and it took almost 25 Minutes to import the file. FYI the files are being uploaded from a web portal in a particular folder and I am importing it using a Stored Procedure in a scheduled Job. I am saving file and sheet names in a table and my SP then extract the pending file and sheet names and prepare a dynamic SQL query and finally execute it. Can you please suggest some other faster way to import xlsx file in sql server or any suggestion for my existing process.
The xlsx files will still be uploaded from a web portal.
Sample Query for my Project looks like this:
INSERT INTO myTable(field1, field2, field3)
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;Database=D:\Manish\SourceFiles\file10.xlsx;HDR=YES;IMEX=1’,
‘SELECT * FROM [items$]’);
This will also work:
INSERT INTO SampleTable (ID, Col1)
select 1, ‘One’ union all
select 2, ‘Two’ union all
select 3, ‘Three’
I have a question – I use this syntax in to load app dictionary. It’s about 5000 records so I have 5 insert statements to overcome 1000 rec limit.
I have the performance issue :
It takes about 7-8 secondsto do the insert – on the same server the same data amount insert as select takes less that a sec.
i have a table that has million records when i try to retrieve data or insert data via dyndns from vb6 its very slow some time query timed out expired error. on local system its working but on remote computer its taking too much time.
can we create a view using ‘select into’ statement and can we create a data type of unsigned int in a table,please
explain us with an example .
SELECT INTO is used to create table not view.
How about inserting into two tables with one query
Insert into table2(childID,action,chagedate)
Select Id,$action,getdate() from (
Insert into table1(col1,col2)
Output inserted.id
Value(@col1,@col2)
)