August 17, 2009 at 11:18 am
I have some problem with selecting columns from table, let us say I have a table with 100 columns and I need only 98 columns to display.
So, what we can do is we can use following query to solve it
SELECT Aa, Ba, Ca,........... FROM temp
In above query we need to write all the name of columns i.e. 98 columns except the unneeded ones.
Here it is very hard for me to write name of all 98 columns, so my question is, is there any way to write a query where we mention only those two columns that we don't need and solve the problem.
For example some thing like
SELECT All but NOT Ia, Ja FROM temp
Thanks in advance
August 17, 2009 at 11:24 am
you can have ssms do that for you. right click on the table name, script table->select to->new query window. then just search and delete your extra columns.
August 17, 2009 at 11:35 am
Another option I use to quickly generate insert/select statements is to generate them dynamically...
Something like this:
DECLARE @TableName sysname;
DECLARE @Sql nvarchar(max);
SET @TableName = 'ProductPhoto';
SET @Sql = '';
SELECT @Sql = @Sql + CASE LEN(@Sql) WHEN 0 THEN '' ELSE ',' END + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
AND COLUMN_NAME NOT IN ('LargePhoto','ModifiedDate');
PRINT @Sql
This will print out the column names (minus the ones you don't want) and you can copy them to your query
August 17, 2009 at 11:51 am
you can also use Query Designer in SSMS (Ctrl+Shift+Q)
Add your table
select the first column by clicking on its name (not the check box)
hold down shift key
go to last column and click its name....all cols will now be highlighted
now click a check box....you will see all columns are selected.
click outside of the table to unhighlight the columns and un check the two you dont work
the select statement will update itself in the designer....click ok to paste into your query
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 17, 2009 at 12:37 pm
I don't want to use SSMS. I want a query because I want to use it not only in MSSQL 2005, but may be in My SQL and Oracle too.
So please provide the queries.
Thanks for effort.
August 17, 2009 at 2:06 pm
santosh.shrestha (8/17/2009)
I don't want to use SSMS. I want a query because I want to use it not only in MSSQL 2005, but may be in My SQL and Oracle too.So please provide the queries.
Thanks for effort.
......seems you forgot to mention this requirement in your original post.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 17, 2009 at 2:09 pm
santosh.shrestha (8/17/2009)
I don't want to use SSMS. I want a query because I want to use it not only in MSSQL 2005, but may be in My SQL and Oracle too.So please provide the queries.
Thanks for effort.
Sorry, but you want us to provide you with what queries? You have been given multiple examples of how to generate a query in SQL Server 2005. This forum is dedicated to SQL Server 2005 and not MySQL (although, we generally will try to help where possible).
You want a simple method available for SQL Server 2005 - from Object Explorer in SSMS, navigate to the table, click the + for the table, click and hold on the columns folder and drag it to a new query window and you'll get all columns (no typing). Now, find the two columns you don't want and remove them.
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 17, 2009 at 3:20 pm
Let us say we have a following table
CREATE TABLE Temp1 (
Aa varchar(10),
Bb varchar(10),
Cc varchar(10),
Dd varchar(10),
Ee varchar(10),
Ff varchar(10),
Gg varchar(10),
Hh varchar(10)
)
Now let us insert some thing in table so
INSERT INTO Temp1 VALUES ('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8')
INSERT INTO Temp1 VALUES ('b1', 'b2', 'b3', 'b4', 'b5', 'b6', 'b7', 'b8')
INSERT INTO Temp1 VALUES ('c1', 'c2', 'c3', 'c4', 'c5', 'c6', 'c7', 'c8')
Now if I want to get the values from all column I can use following query
Query 1:
SELECT * FROM Temp1
But if I want to get values from all column except Hh, I will have to write a query as below
Query 2:
SELECT Aa, Bb, Cc, Dd, Ee, Ff, Gg FROM Temp1
Now my problem is if I have 100 columns and if I need only 98 out of 100 columns, then unfortunately, I have write name of all 98 columns in the query as shown above.
Since there are multiple tables some are in MS SQL and some are in MySQL and it is very hard for me to write queries for all table, so want query like Query 2, but the condition is we don't need to write name of all columns. May be can work with wild card and thing like that.
I hope I explained it correctly.
Thanks for the help.
August 17, 2009 at 3:36 pm
santosh.shrestha (8/17/2009)
Let us say we have a following tableCREATE TABLE Temp1 (
Aa varchar(10),
Bb varchar(10),
Cc varchar(10),
Dd varchar(10),
Ee varchar(10),
Ff varchar(10),
Gg varchar(10),
Hh varchar(10)
)
Now let us insert some thing in table so
INSERT INTO Temp1 VALUES ('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8')
INSERT INTO Temp1 VALUES ('b1', 'b2', 'b3', 'b4', 'b5', 'b6', 'b7', 'b8')
INSERT INTO Temp1 VALUES ('c1', 'c2', 'c3', 'c4', 'c5', 'c6', 'c7', 'c8')
Now if I want to get the values from all column I can use following query
Query 1:
SELECT * FROM Temp1
But if I want to get values from all column except Hh, I will have to write a query as below
Query 2:
SELECT Aa, Bb, Cc, Dd, Ee, Ff, Gg FROM Temp1
Now my problem is if I have 100 columns and if I need only 98 out of 100 columns, then unfortunately, I have write name of all 98 columns in the query as shown above.
Since there are multiple tables some are in MS SQL and some are in MySQL and it is very hard for me to write queries for all table, so want query like Query 2, but the condition is we don't need to write name of all columns. May be can work with wild card and thing like that.
I hope I explained it correctly.
Thanks for the help.
I understood your question before, and everyone else has already answered telling you it is not possible.
First, nobody here would ever suggest using '*' in production code. There are just too many issues related to using it to ever have that in production code. It is always much better to specify the columns that are required than to use a wildcard.
Second, all of the suggested options show how to avoid actually having to type the column names for the query. Drag & drop is not hard at all - and besides, you really shouldn't have to repeat this too many times, and if you do - then you are forgetting about copy & paste.
And finally, really think about what you are asking here. If it were possible, then what would you want to happen when the table(s) you are accessing are modified and columns added or removed? Should your query include the new columns? Exclude them? What happens when a column is removed or renamed and your code is assuming that the column exists?
These are just some of the reasons why using '*' or what you are asking for are not recommended approaches to coding. What you could do is go out and purchase a tool like SQL Assistant and use that tool to build your queries. The tool can be configured to what you are asking, except it is going to generate the SQL with all columns - and you would have to remove the columns you don't want.
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 17, 2009 at 8:48 pm
Thank you very much for your reply, I got your idea. I didn't knew about the disadvantage of using wild card till today.
So this has been good learning step for me.
Thanks again for every bodies effort.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply