CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Michael
Michael

Reputation: 23

How to batch export only 10 rows from Oracle SQL developer query results for each table to separate excel files

I have 50 tables on a schema on Oracle server. Each table has roughly 1 million rows. I want to export all tables to separate excel files. I only need 10 rows for each table. I know I can select 10 rows and manually export it to excel file one by one. Is there any way to set exporting only 10 rows in Export Wizard, and export 50 tables to separate excel files in batch (automatically)? Thanks.

Upvotes: 2

Views: 6957

Answers (2)

Travis Heeter
Travis Heeter

Reputation: 14054

This is not exactly what you asked, but when I asked Google a similar question I ended up here so I figured I'd add what I did to help the next person.

If you want to see an INSERT for one or many rows in SQL Developer:

  1. Tools > Database Export

    Database Export Location

  2. Select your connection, Uncheck Export DDL & Change "Save As" to "Worksheet"

    Export Source Settings

  3. On "Types to Export" select Tables only

    Types to Export Settings

  4. On "Specify Data" Type the name of your Table in "Name", then click "Lookup"

    Specify Data 1

  5. Click your table then move it down

    Specify Data 2

  6. Click The empty Object Where space next to the Database Object:

    Specify Data 3

  7. Click the Pencil

    Specify Data 4

  8. This will bring up the Data Where Window (I have blocked my data just in case). Click into the Object Where text input:

    Data Where 1

  9. Enter your clause that will match whatever rows you want to see the INSERT statement for. For me it was PROC_ID=0000.

  10. Click Refresh and a list of your rows should appear. Check to make sure your WHERE got the correct tables.

  11. Click OK, Next, then Finish and you should get a new worksheet with the insert command. If it's not what you expected try different WHERE clauses.

My case was a little different. I wanted to just get the INSERT for one row in a complex table. Then I'll use the INSERT as a template for adding new rows. And since I'm doing this, it's better for me to get it in SQL Developer instead of separate files.

Thanks to @thatjeffsmith for steering me in the right direction.

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22427

Tools - Database Export

enter image description here

Note the output type is set to Separate Files and we're pointing to a directory.

If you set it to a single file, you'll get one excel file with 50 worksheets.

Pick your tables, and set GLOBAL WHERE CLAUSE

Click Next, then Finish, and you're off to the races.

enter image description here

Upvotes: 11

Related Questions