Insert multiple records 1

[Andrzejek]

Is there a way to insert multiple records into a table with one Insert statement?

Something like:[pre][blue]INSERT INTO MyTable (ID, F_NAME, AGE) VALUES
(2, 'SUSIE Q', 24),
(3, 'BOB Q', 25),
(4, 'Bill Z', 15)[/blue]
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

None that I'm aware of. Your best bet (barring a better suggestion) would be to use SQL*Loader or Excel.

 

[Andrzejek]

This is just a matter of curiosity.
I think I’ve seen this syntax somewhere on the Web, but I’ve never was able to make it work.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

If your data is already in tables, you can try:

INSERT INTO myTable(id, f_name, age)
(SELECT id_source, f_name_source, age_source
   FROM source_tables
  WHERE filter_conditions);

Like Andy, I had seen samples of inserting/joining with tuples, but they never worked right for me.
Something to try again when I have time.

 

[Andrzejek]

carp,
I use your approach all the time.
And I see I am not the only one who have seen this 'other' way that does not work. Too bad. It could be useful, especially if it would improve the performance of inserting many records at once instead of one-by-one.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mikrom]

For example on DB2 the syntax from above is working well:

INSERT INTO MyTable (ID, F_NAME, AGE) VALUES 
  (2, 'SUSIE Q', 24), 
  (3, 'BOB Q', 25),
  (4, 'Bill Z', 15)

But on Oracle syntax like this is required:

INSERT ALL
  INTO MyTable (ID, F_NAME, AGE) VALUES (2, 'SUSIE Q', 24) 
  INTO MyTable (ID, F_NAME, AGE) VALUES (3, 'BOB Q', 25)
  INTO MyTable (ID, F_NAME, AGE) VALUES (4, 'Bill Z', 15)
SELECT * FROM DUAL

 

[Andrzejek]

Thank you mikrom, it works like a charm.
I just don't get why the '[tt]SELECT * FROM DUAL[/tt]' part at the end is needed, but then... who cares, right?

Do you know if the performance is improved comparing to individual INSERT INTO statements?

This also works, as long as the order and number of data 'pieces' are correct:

INSERT ALL
  INTO MyTable VALUES (2, 'SUSIE Q', 24) 
  INTO MyTable VALUES (3, 'BOB Q', 25)
  INTO MyTable VALUES (4, 'Bill Z', 15)
SELECT * FROM DUAL

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mikrom]

Andrzejek,
[tt]'SELECT * FROM DUAL'[/tt] part is needed, because INSERT ALL syntax requires a subquery - see:

https://www.oracletutorial.com/oracle-basics/oracle-insert-all/

I have no clue about the performance of INSERT vs INSERT ALL but look here:

https://stackoverflow.com/questions/19559418/oracle-insert-all-vs-insert

 

[carp]

Thank you, Mikrom! This gives me something new to explore.

 

[mikrom]

Other way how to insert multiple records into a table with one INSERT statement could be

INSERT INTO MyTable (ID, F_NAME, AGE) 
  SELECT 2, 'SUSIE Q', 24 FROM DUAL
  UNION ALL 
  SELECT 3, 'BOB Q', 25   FROM DUAL
  UNION ALL 
  SELECT 4, 'Bill Z', 15  FROM DUAL