August 28, 2012 at 7:38 am
Hi All,
I need one help in formulating select query.
I have a table with 50 columns in it, now i want to retrieve not all the columns from this table.
i.e, say i have col like: a,b,c,d & i want a select query without column c,d. I know using simply select a, b frm tbl will serve the purpose, but think when there are 50 columns and you want to retrieve only 40 from them.
Is there any specific TSQL syntax available that will full fill the requirement.
Thanks
Sam
August 28, 2012 at 7:44 am
No. There is no specific syntax available for this purpose.
You will have to manually type in the column names or use some tools like SQL Prompt which will list out the column names and help you in reducing the typing effort.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 28, 2012 at 7:46 am
You could right click on the table in SSMS and select SCRIPT TABLE AS / SELECT To / New query editor window.
That will build a SELECT query for you containing all the column names in the table - so you can delete the ones you do not need.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 28, 2012 at 7:47 am
There's no T-SQL syntax, but there are tricks in SSMS.
You can right click on the the table in the object explorer and choose: Script Table as -> SELECT To -> Clipboard. This way you can paste your code wherever you need and just erase the unwanted columns.
Another way is to drag the Columns folder of the table to the place you need it and erase the unwanted columns.
August 28, 2012 at 7:48 am
In SSMS expand the database and table that you want and drag the column folder of the table you want to query into the query window. it will list all of the columns. You can then delete the ones you don't want instead of typing all of out.
August 28, 2012 at 7:49 am
Thanks Phil for your input.
August 28, 2012 at 7:50 am
sam 55243 (8/28/2012)
Hi All,I need one help in formulating select query.
I have a table with 50 columns in it, now i want to retrieve not all the columns from this table.
i.e, say i have col like: a,b,c,d & i want a select query without column c,d. I know using simply select a, b frm tbl will serve the purpose, but think when there are 50 columns and you want to retrieve only 40 from them.
Is there any specific TSQL syntax available that will full fill the requirement.
Thanks
Sam
You will need to specify each column you want to retrieve. There are two ways to do this. For a simple select, you can have SSMS create the SELECT query for you by right clicking on the table, click on Script Table as, SELECT To, then your choice of New Query Window, File ..., Clipboard, Agent Job .... At this point you can then edit the query to remove the columns you don't want.
An alternative to that is to expand the table, press down and hold the left mouse button while you have the mouse pointer on the Columns folder, drag that to the query window and release the the left mouse button. Then all you need to do is edit the list of columns to remove those you don't want.
August 28, 2012 at 7:51 am
You can also expand the table in SSMS. You can click and drag the "Columns" folder to a query window and you will have comma separated list of all the columns. Then just remove the columns you don't want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2012 at 7:52 am
Sean Lange (8/28/2012)
You can also expand the table in SSMS. You can click and drag the "Columns" folder to a query window and you will have comma separated list of all the columns. Then just remove the columns you don't want.
WOW Luis said that already and Lynn posted his before I hit submit...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply