Insert multiple values into a table

Question

How do I insert several values into a table, but into a single one query of sql?

Answer 1

SQL Server, Postgresql, Mariadb, DB2 and Mysql:

INSERT INTO TESTE VALUES (1,'ABACAXI'), (2,'BATATA');

Examples:

• SQL Server in the SQL Fiddle.

• MySQL in the SQL Fiddle.

• PostgreSQL in the SQL Fiddle.

Oracle:

You can use the INSERT ALL:

INSERT ALL
   INTO TESTE VALUES (1,'ABACAXI')
   INTO TESTE VALUES (2,'BATATA')
SELECT 1 FROM DUAL;

See working on SQL Fiddle.

Mongodb

db.teste.insert([
    {"id": 1, nome: "ABACAXI"},
    {"id": 2, nome: "BATATA"}
]);

---

_{As did not inform the SGDB put the main ones, if not the case, I update later.}

Answer 2

With Sql Server, 2 suggestions:

declare @sql nvarchar(max)

set @sql = N'insert into tabela (coluna1, coluna2) values (''coluna1'', ''coluna2'');
             insert into tabela (coluna1, coluna2) values (''coluna1'', ''coluna2'');
             insert into tabela (coluna1, coluna2) values (''coluna1'', ''coluna2'');'

exec sp_executesql @sql

or

insert into tabela (coluna1, coluna2)
select coluna1, coluna2 from tabela2