SQLDev Tip and Trick: Drag and Drop to Worksheet

thatjeffsmith SQL Developer 23 Comments

Tell Others About This Story:

Dragging and dropping tables to the worksheet is a pretty obvious feature – it copies the object names over for you. But did you know you had the option to get more bang for your buck?

Drag and drop preferences

Once you do the drag, you can select what you want copied to the Worksheet. If you don’t see this dialog, open the Preferences and go to the Database – ‘Drag and Drop’ page. Personally I would set it to what I want and toggle OFF the ‘prompt every time.’ I like getting the JOINs written out for me.

For this example I’m only dragging over 2 tables, so the benefit over manual typing is decreased. However, imagine instead I am dragging 15 tables over. Then I think this exercise starts to get exciting.

Just curious, what’s the largest number of TABLES you’ve seen joined in a single statement? 15 seems to be the magic number for smaller audiences. However I’ve ran into a 3 VIEW JOIN that had 50 joined TABLEs each for the 3 underlying VIEWs, so I know there’s some pretty crazy stuff out there.

Ok, back on our ‘trick’ – here is what you get after dragging the tables over.

Woohoo - no SELECT *!

OK, I don’t want all those COLUMNs, so I toggle over the Query Builder view…

Tuning Tip: Query only what you need.

…and toggle off the columns I don’t want. For larger tables, right-click on the TABLE and ‘Uncheck All’, then come back in and toggle the COLUMNs you want. Go back to the Worksheet and…

Less typing, more clicking = less time, more code

…voila, our statement is ready!

You can read more about using the Query Builder and Worksheet together here.

Tell Others About This Story:

Comments 23

  1. I am not seeing the “Join” dialog after selecting 2 or more tables.

    I have gone to Tools > Preferences > Database > Drag And Drop and selected “Select” + “Join” radio buttons & unticked “Prompt every time” & saved my changes.

    Thanks,
    Dave

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Thanks for the quick response.

        The following is what I am seeing:

        —————–
        SQLDev Version 17.3.1.279; Build 279.0537

        Steps:
        1) Start SQLDev & create new ‘SQL worksheet’ (Alt-F10)
        2) Click into ‘Query Builder’
        3) Tag 3 tables
        4) Drag the selections to ‘Query Builder’
        5) Dialog does NOT display
        6) Selected tables display in the ‘Query Builder’ and are not linked

        Thanks,
        Dave

      2. thatjeffsmith Post
        Author
  2. Query builder suddenly changed, and it doesn’t show the table columns. Each table is represented by only the table title with no columns.

    I just closed out of sql developer for lunch and came back to go to work. How can I fix this?!

    john3

    1. thatjeffsmith Post
      Author
      1. I’m using 4.1.4.21, and it looks like Mgt. doesn’t want me upgrading.

        Note: Column names reappeared later! I did see a msg. something about connection getting closed? Could that relate?

      2. If connection closing causes this, I have been unable to find a straightforward way to ‘bring back’ column_names, after this ‘breakage.’

        Are there instructions?

        john3

      3. thatjeffsmith Post
        Author
      4. I can reconnect to the database. I need to know how to reconnect in such a way that the columns immediately ‘come back.’

        So far, no luck.
        john3

      5. thatjeffsmith Post
        Author
  3. “…and toggle off the columns I don’t want. For larger tables, right-click on the TABLE and ‘Uncheck All’, then come back in and toggle the COLUMNs you want. ”

    Jeff,
    Where exactly can I right-click on TABLE to select Uncheck All?

    Thank you,
    john3

    1. thatjeffsmith Post
      Author
  4. Why don’t I get the ‘a.’ and ‘b.’ prefix as shown here?

    When I drag a table to place in query builder, it prefixes with ‘tablename.’ instead.

  5. Hi Jeff,

    Thank you for the blog and your time helping us SD users.

    I am running SD 4.0.2.15 on OS X 10.9.5.

    I cannot find a way to use the drag and drop functionality to create a SELECT statement with a join.

    I click on table 1, I click on table 2. This always opens up the table interface in a new tab.

    I click on the original tab containing the SQL worksheet. Both tables are still highlighted in the connection navigator. Now when I click either table to do the ‘click and drag’ the other table loses focus and the “Join” radio button is greyed out.

    Am I doing something wrong?

    Jack

    1. thatjeffsmith Post
      Author
  6. Hello Jeff,

    I’m completley new with sql developer

    May you can help me

    I have the Problem, that if i take some tables with the drag and drop select join option, theres no row inthe result for an join just only the select commands for the tables which i using …

    What I’m doing wrong ?

    best regards john s.

    1. thatjeffsmith Post
      Author
  7. Indeed, you’re right. Thank you 🙂

    I didn’t have the latest version of SQL Developer. Now that I have updated, I have the ANSI syntax for JOINs and the FULL OUTER JOIN available.

    Thank you

  8. Unfortunately, I haven’t found any way to generate ANSI syntax for JOIN… And no way to have a FULL OUTER JOIN. Maybe in a future release?

    Braim

    PS: Nice blog 🙂

    1. JeffS Post
      Author

      Actually you can do that today. Right click on the join and toggle on the ‘SELECT all rows’ for both sides. That will give you something like this –
      SELECT…
      DEMO_ORDERS.CUSTOMER_ID
      FROM DEMO_ORDER_ITEMS
      FULL JOIN DEMO_ORDERS
      ON DEMO_ORDERS.ORDER_ID = DEMO_ORDER_ITEMS.ORDER_ID

      I’ll send you a screenshot to your gmail.

Leave a Reply

Your email address will not be published. Required fields are marked *