How to Multiple Insert Multiple Records

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)...

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.

How should I multiple insert multiple records?

If I were you I would not use either of them.

The disadvantage of the first one is that the parameter names might collide if there are same values in the list.

The disadvantage of the second one is that you are creating command and parameters for each entity.

The best way is to have the command text and parameters constructed once (use Parameters.Add to add the parameters) change their values in the loop and execute the command. That way the statement will be prepared only once. You should also open the connection before you start the loop and close it after it.

SSMS / TSQL - Insert multiple records for every record found in query

WITH xQ(UUID,Column2,Column3,Column4,Column5) AS 
( 
    SELECT
        NEWID(),
        Column1,
        Column2,
        Column3,
        Column4,
        Column5 
    FROM
        table1
    WHERE Column1 IS NOT NULL
    AND Column1 != Column4
)

INSERT INTO table1
( 
    Column1,
    Column2,
    Column3,
    Column4,
    Column5
)
SELECT
    UUId, 
    Column1,
    Column3,
    Column4,
    Column5 
FROM xQ
UNION
SELECT
    UUId,
    Column2,
    Column3,
    Column4,
    Column5 
FROM xQ

When inserting multiple record into a SQL table it does not follow the order

Your select statement explicitly says: "I do not care about the order!" - it does so by omitting the ORDER BY clause.

If you say SELECT * FROM [category] ORDER BY [category_id] you will get an ordered result set along the lines of

1, 'Laptops and Accessories'
2, 'AI Development Kit'
3, 'Cameras and Lens'

Which is exatly the order you gave in your INSERT statement.

SELECTs in SQL without an ORDER BY clause are not only not expected to be in some order, they are also not expected to have the same order when called multiple times.

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 :)

How to insert multiple records with same column in one shot?

Correct syntax of insert statements for your records -

INSERT INTO master_tbl (DESC,CODE) VALUES ('A','1');
INSERT INTO master_tbl (DESC,CODE) VALUES ('A','2');

This is a good way to insert records. If you have finite number of values, you can go with INSERT..SELECT with Cartesian join.

Sample SQL -

insert into master_tbl(desc, code)
select t1.col1, t2.col1
(select 'A' as col1 from dual
union
select 'B' from dual
union
select 'C' from dual
union 
select 'D' from dual) t1
join
(select '1' as col1 from dual
union
select '2' from dual
union
select '3' from dual
union 
select '4' from dual) t2;

Are you looking for a help to create 'insert' script out of excel sheet?

You can use CONCATENATE function in excel to place your cell values. Assuming these two columns are placed in A and B cells in excel. You can extend this function to all records in your sheet.

=CONCATENATE("INSERT INTO master_tbl (DESC,CODE) VALUES ('",A2,"','",B2,"');")

---

Related Topics