Click here to Skip to main content
15,071,262 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
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
SQL
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

Thanks.
Posted
Updated 19-Jun-12 2:49am
v2
Comments
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)
shek124 19-Jun-12 9:11am
   
For DB optimization perspective, why you created the table as more number of columns. Better you will make optimize the table/db first.

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:

http://www.red-gate.com/products/sql-development/sql-prompt/[^]

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).
   
Comments
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:

SQL
select * from all_tab_cols
where upper(table_name) = ''
   

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