Click here to Skip to main content
15,505,786 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a table 'myTable' containing 50 fields/columns (like column1,column2,......, column 49,column50).
I want to fetch all records for 45 columns only.

so, what is the optimized query for this?

currently, I'm using Query like
Select column1,column2,column3,.....,column48,column49 from myTable 

Is there any other way to shortened this query?
Here not required to fetch column10,column20,column30,column40,column50

Updated 19-Jun-12 3:49am
Prasad_Kulkarni 19-Jun-12 8:45am    
What's problem with SELECT * FROM MyTable??
amolpatil2243 19-Jun-12 8:48am    
yes, use SELECT * FROM MyTable and exclude the columns..
ashriv 19-Jun-12 9:12am    
what is the way to exclude the column? since Here not required to fetch column10,column20,column30,column40,column50
Oshtri Deka 19-Jun-12 8:54am    
Why don't you rewrite your query to return only columns you are interested in?
This straight-forward -- SELECT [columns you want] FROM [table of your choosing].
So what's the problem?
ashriv 19-Jun-12 9:16am    
problem is writing a long select statement. is there any way to exclude the column?
something like --
select * from myTable where column not in (column10,column20,column30,column40,column50)

Listing out each column name is the way you need to do it in order to have an optimized query. However, that doesn't mean you need to type it all out. You've listed a number of different SQL flavors (Oracle, MySQL, MSSQL, etc.) so this will be a bit different for each but in Microsoft SQL in the Management Studio (SSMS), you can right-click on the table and say "Script Table As..." and then "SELECT To" and finally "New Query Editor Window". This will give you a full SELECT statement. Now you just need to remove your five columns and you will be set.

Another way to do this, although not free, is to use SQL Prompt from Red Gate:[^]

This tool will give you a lot of features in SSMS. One is that when you say "SELECT *", you can hit tab twice after the star and it will give you the column names (once you have specified the table in the FROM statement).
Share this answer
Manas Bhardwaj 19-Jun-12 10:41am    
Good +5!
Maciej Los 19-Jun-12 16:12pm    
Good point, my 5!
I actually think that you could pass something like a string of concatenated
values of your Column1..50 (excluding irrelevant) as a single column of literal type or bucket of this columns (say 5-8 clusters) and process them accordingly on the destination process.

Another option (if you are using Oracle DBMS) is to use dynamic SQL after listing all the columns in table or view:

select * from all_tab_cols
where upper(table_name) = ''
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900