SQL Insert Multiple Rows

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

Mysql insert multiple rows, if duplicate value column then update

You must create unique index which will detect the duplication:

CREATE UNIQUE INDEX idx ON test (customer_id, textkey);

Now you can use INSERT .. ODKU:

INSERT INTO test (customer_id, textkey, value) VALUES
(1, 'text1', 'valueX'), 
(1, 'text3', 'valueY'), 
(1, 'text5', 'value5'), 
(1, 'text6', 'value6')
ON DUPLICATE KEY UPDATE
value = VALUES(value);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6dcc351706f574f4c9f13c1fc95b9225

Azure Synapse unable to insert multiple rows into table

1. use ‘select’ statement instead of ‘values’ statement.

insert into tb_test01(userid, username)

select '1','a'

union all

select '2','b'

2. Considering performance, please use bulk loading instead of singleton INSERTS if you need to load many rows. Please check this document for more information which has following details:

Group INSERT statements into batches

A one-time load to a small table with an INSERT statement or even a periodic reload of a look-up may perform just fine for your needs with a statement like INSERT INTO MyLookup VALUES (1, 'Type 1'). However, if you need to load thousands or millions of rows throughout the day, you might find that singleton INSERTS just can't keep up. Instead, develop your processes so that they write to a file and another process periodically comes along and loads this file.

Inserting multiple rows into some table using values from select statement

Yes, but you need to be careful how you do it. In this case, it appears you want a cross join:

INSERT INTO SomeTable (UserID, ModuleID, ModuleRights)
    SELECT u.UserId, v.ModuleID, v.ModuleRights
    FROM Users u CROSS JOIN
         (VALUES (1, 15), 
                 (2, 1), 
                 (4, 3), 
                 (8, 7)
         ) v(ModuleID, ModuleRights);

Insert multiple rows into a table with a single statement

You can have multiple inserts by not using VALUES keyword. You need to specify same number of columns on your source table.

INSERT INTO sample_tag (sample_id, tag_id) 
SELECT sample_id, tag_id from sample where sample_id<=500

How do I insert multiple rows from one table into a struct column of a single row of another table?

It's because ARRAY() is not an aggregation function. You should ARRAY_AGG() along with GROUP BY to group details for each employee into an array.

SELECT EmployeeNumber,
       ARRAY_AGG((SELECT AS STRUCT EmployeeFirstName, EmployeeLastName, EmployeeAddress)) AS employeeDetails
   FROM `dev.try_sbx.sourceTableEmployee`
  GROUP BY 1;

More preferred way is :

SELECT EmployeeNumber,
       ARRAY_AGG(STRUCT(EmployeeFirstName, EmployeeLastName, EmployeeAddress)) AS employeeDetails
   FROM `dev.try_sbx.sourceTableEmployee`
  GROUP BY 1;

output:

Insert multiple values into the same row for a single foreign key

You need a table ReservationServices, defined something like this:

create table ReservationServices (
    ReservationServiceId int identity primary key,
    ReservationId int not null references reservations(reservationid),
    ServiceId int not null references services(serviceid);
);

Then your data would look like:

ReservationServiceId     ReservationId    ServiceId
       1                     10               1 
       2                     10               2 
       3                     20               1 
       4                     20               3
       . . .

So, inserting a new service for a reservation is, well, as simple as inserting a new row in this table.

---

Related Topics