August 13, 2010 at 11:48 am
A business analyst raised this issue: what is an efficient way to do a SELECT against specific columns in a table. For example, we have one table with at least 50 columns, with “gooblety-gook” names. She need to pull in, say, 15 columns, not the other 35.
Approach 1: I asked her to open Management Studio, expand the database, table name, and finally the Columns collection. The open a new Query pane, type SELECT and drag-and-drop the columns names, one by one, from the left pane into the Query windows. She thought that was too error prone and tedious to do for 15 columns.
Approach 2: I asked her to right-click the table name, select “Select Top 1000 Rows” from the context menu. This generates the full SELECT query and she can manually delete the unwanted fields. She wasn’t thrilled by that answer either.
Approach 3: I asked her if she was familiar with the SQL “Query Builder”. NO, so that approach went down in flames.
Is there a simple way, such as by using the Ctrl or Shift keys in Management Studio, that she can pull only the fields that interest her?
TIA,
Barkingdog
August 13, 2010 at 12:14 pm
why not suggest to run sp_help tablename and then you will get all details for that table including individual column name. then you can select required columns and copy paste in your select query
----------
Ashish
August 13, 2010 at 2:15 pm
The simplest and fastest (for me), is to just open a query window and start typing.
Now, if this is a case where the user is not really sure what the column names are and want something that will make it easier to identify the columns - then you want Intellisense enabled.
That will give here a drop-down where she can select the columns she wants, as she is typing out the query. If you are using the 2008 client tools against a 2005 instance - this won't work. However, you can always go out and purchase SQL Prompt from Redgate - or SQL Assistant from SoftTree Technologies.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2010 at 2:51 pm
Barkingdog (8/13/2010)
A business analyst raised this issue: what is an efficient way to do a SELECT against specific columns in a table. For example, we have one table with at least 50 columns, with “gooblety-gook” names. She need to pull in, say, 15 columns, not the other 35.Approach 1: I asked her to open Management Studio, expand the database, table name, and finally the Columns collection. The open a new Query pane, type SELECT and drag-and-drop the columns names, one by one, from the left pane into the Query windows. She thought that was too error prone and tedious to do for 15 columns.
Approach 2: I asked her to right-click the table name, select “Select Top 1000 Rows” from the context menu. This generates the full SELECT query and she can manually delete the unwanted fields. She wasn’t thrilled by that answer either.
Approach 3: I asked her if she was familiar with the SQL “Query Builder”. NO, so that approach went down in flames.
Is there a simple way, such as by using the Ctrl or Shift keys in Management Studio, that she can pull only the fields that interest her?
TIA,
Barkingdog
It sounds like she's just trying to get out of having to do the querying herself.
None of the above take a lot of time or are particularly difficult.
Still, if you want to help, assuming you can't just fix the table naming, maybe write a view for her that has easier to tell field names (and/or excludes fields that she generally doesn't need to see). Or just have her query all fields, paste the output into excel, then hide any column she doesnt care about.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply