28 April 2007

SQLDeveloper Paste Multiple Rows

If you're using SQLDeveloper, you can copy and paste multiple rows into a table in one operation, which makes it easy to load small amounts of data into a database for testing.

  1. Create your tabular data in Excel.
  2. Copy your data into the clipboard.
  3. In SQLDeveloper, open the Data tab of the required table.
  4. Add as many rows as required by repeatedly pressing the Insert Row button.
  5. Paste your data into SQLDeveloper.

If you look in the SQLDeveloper console page, you can see a sequence of INSERT statements followed by a COMMIT statement.

It seems that you can only insert a row if your database server supports transactions, so this trick works for Oracle but not for MySQL.