SQL INSERT INTO help please

Question

ok so i feel like im missing something so trivial but i dont know what im doing wrong. Im just trying to insert some data into a table but im getting an error

INSERT INTO SHOPS
VALUES
('12345', '10 smith street', 'brunswick', '3030', 
'0404123123', 'shop1@shop.com'**),**
('65435', '10 road street', 'fitzroy', '3030', 
'0404546564', 'shop2@shop.com'),
('34232', '10 car street', 'carlton', '3030', '0404455432', 
'shop3@shop.com');

So im getting an error on the bracket and comma i bolded on the 4th line.

If anyone knows how to fix this i would be very grateful.

You can't insert more than one row at a time with an `insert into ... VALUES` statement. — , Jun 02 '20 at 02:32
Use multiple INSERT statements, or use INSERT INTO (....) SELECT ... FROM DUAL UNION ALL SELECT ... FROM DUAL UNION ALL (etc.) Follow the link in the second Comment, you will see an example. — , Jun 02 '20 at 02:35

score 0 · Answer 1 · answered Jun 02 '20 at 02:49

If looks like you're using the MySQL format for a multi-row INSERT statement. This is not correct for use with Oracle. It should be

INSERT ALL
  INTO SHOPS  (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
       VALUES ('12345', '10 smith street', 'brunswick', '3030', '0404123123', 
               'shop1@shop.com'),
  INTO SHOPS  (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
       VALUES ('65435', '10 road street', 'fitzroy', '3030', '0404546564', 
               'shop2@shop.com'),
  INTO SHOPS  (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
       VALUES ('34232', '10 car street', 'carlton', '3030', '0404455432', 
               'shop3@shop.com')
SELECT * FROM DUAL;

As an alternative you can use a different form of the INSERT statement:

INSERT INTO SHOPS (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
  SELECT '12345', '10 smith street', 'brunswick', '3030', '0404123123', 
               'shop1@shop.com' FROM DUAL UNION ALL
  SELECT '65435', '10 road street', 'fitzroy', '3030', '0404546564', 
               'shop2@shop.com' FROM DUAL UNION ALL
  SELECT '34232', '10 car street', 'carlton', '3030', '0404455432', 
               'shop3@shop.com' FROM DUAL

Or you can use a MERGE statement:

MERGE INTO SHOPS s
  USING (SELECT '12345' AS FIELD1, '10 smith street' AS FIELD2, 'brunswick' AS FIELD3,
                '3030' AS FIELD4, '0404123123' AS FIELD5,
                'shop1@shop.com' AS FIELD6 FROM DUAL UNION ALL
         SELECT '65435', '10 road street', 'fitzroy', '3030', '0404546564', 
               'shop2@shop.com' FROM DUAL UNION ALL
         SELECT '34232', '10 car street', 'carlton', '3030', '0404455432', 
               'shop3@shop.com' FROM DUAL) d
     ON (d.FIELD1 = s.FIELD1)
  WHEN NOT MATCHED THEN
    INSERT (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
    VALUES (d.FIELD1, d.FIELD2, d.FIELD3, d.FIELD4, d.FIELD5, d.FIELD6);

I used FIELD1, FIELD2, ..., FIELD6 since you didn't includes a field list with your INSERT statement - replace these with the correct field names. The MERGE statement assumes that FIELD1 is the primary key on SHOPS - replace it with the correct field name.

Why would anyone use MERGE instead of INSERT? – Jun 02 '20 at 03:17 — , Jun 02 '20 at 03:17

SQL INSERT INTO help please

1 Answers1