SQLDev Tip and Trick: Drag and Drop to Worksheet

thatjeffsmith SQL Developer 7 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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 7

  1. 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
  2. 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
  3. 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

  4. 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 *