Inserting Multiple Rows in a Single SQL Query

Insert multiple rows of data in a single SQL statement

Multi-row insert has been part of the SQL standard since SQL-92, and many of the modern DBMS' support it. That would allow you to do something like:

insert into MyTable ( Name,     Id,   Location)
             values ('John',    123, 'Lloyds Office'),
                    ('Jane',    124, 'Lloyds Office'),
                    ('Billy',   125, 'London Office'),
                    ('Miranda', 126, 'Bristol Office');

You'll notice I'm using the full form of insert into there, listing the columns to use. I prefer that since it makes you immune from whatever order the columns default to.

If your particular DBMS does not support it, you could do it as part of a transaction which depends on the DBMS but basically looks like:

begin transaction;
insert into MyTable (Name,Id,Location) values ('John',123,'Lloyds Office');
insert into MyTable (Name,Id,Location) values ('Jane',124,'Lloyds Office'),
insert into MyTable (Name,Id,Location) values ('Billy',125,'London Office'),
insert into MyTable (Name,Id,Location) values ('Miranda',126,'Bristol Office');
commit transaction;

This makes the operation atomic, either inserting all values or inserting none.

Insert multiple rows in a single query using results of a select statement

You can unpivot using cross apply:

insert into @stringList (val) 
    select v.col
    from table1 t1 cross apply
         (values (t1.col1), (t1.col2)) v(col)
    where t1.id = something;

Insert multiple rows with single dynamic data

Simply define the correct columns in the select list:

INSERT INTO dbo.tblEmpDetails (EmpId, StateId, CountryId, Comments)
SELECT [<ReturnedColumn>], @StateId, @CountryId, @Comments
from dbo.FN_ListToTable (',', @EmpIds);

Also note that SQL Server has a built-in string_split() function.

SQL Query to INSERT multiple rows with SELECT

If I understand your question correctly, you are wanting to do a query on table1 that returns multiple rows, and then insert those into table2 in a single loop. That's the INSERT INTO SELECT statement:

  INSERT INTO table2
     (name, email, phone)
     SELECT name, email, phone
     FROM table1;

It can be modified to grab specific results as well:

  INSERT INTO table2
     (name, email, phone)
     SELECT name, email, phone
     FROM table1
     WHERE name = 'target person';

More information can be found at http://dev.mysql.com/doc/refman/5.7/en/insert-select.html and http://www.w3schools.com/sql/sql_insert_into_select.asp.

EDIT:

Based on your comment, it sounds like you're trying to do this:
SQL split values to multiple rows.

I can't think of a situation where you'd actually want to do that, as you can access all of the data in your existing table as is, and it seems to be bad practice to split data in the way you're requesting. However, the solutions in the above thread should be applicable to what you're trying to do.

Ultimately, you may want to look at how you're actually retrieving the data. Modifying that code would be a better idea :)

Inserting multiple rows in a single Oracle SQL query:

You where close, but you have much to learn.

Here is how you could do it:

INSERT INTO "SCOTT"."GREATCOLOR1" (COLOR, PAUL, JOHN, TIM, ERIC)
          select 'White', '1', '5', '1', '3' from dual
union all select 'Yello', '8', '4', '3', '5' from dual
union all select 'Black', '2', '2', '9', '1' FROM dual
;

How to insert multiple rows in a table with single row?

Yes (assuming you are on SQL Server 2008 or newer and don't have more than 1000 columns per row).

You can insert rows with the same schema by separating the value blocks with commas.

INSERT INTO tableA (name)
VALUES ('A'), ('B'), ('C')...

The way you're currently doing it is attempting to add each row as a column.

Insert multiple rows WITHOUT repeating the INSERT INTO ... part of the statement?

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question (you just need to add a comma to separate each values statement)...

---

Related Topics