May 18, 2012 at 12:00 am
hi all,
i have doubt regarding select statement, i have one table with 46 columns , i want select only 42 columns.but iam not like to write
select col1,col2...........col42 from mytable.
any another solution for that...............?, plz help me.
regards
prasad
Prasad.N
Hyderabad-India.
May 18, 2012 at 12:03 am
Do you know the names of the 4 columns that you wanna exclude?
You can use dynamic sql to do that, if you know the names of the columns u wanna exclude.
May 18, 2012 at 12:09 am
Prasad.N (5/18/2012)
hi all,i have doubt regarding select statement, i have one table with 46 columns , i want select only 42 columns.but iam not like to write
select col1,col2...........col42 from mytable.
any another solution for that...............?, plz help me.
regards
prasad
You can create a view for this requirement.
May 18, 2012 at 12:11 am
Suresh B. (5/18/2012)
Prasad.N (5/18/2012)
hi all,i have doubt regarding select statement, i have one table with 46 columns , i want select only 42 columns.but iam not like to write
select col1,col2...........col42 from mytable.
any another solution for that...............?, plz help me.
regards
prasad
You can create a view for this requirement.
Even for the view you will still have to type all the 42 columns.
May 18, 2012 at 12:17 am
select ',' , column_name from information_schema.columns where table_name = 'x'
That will give you a result set which has columns listed
, columnA
, columnB
, columnC
Modify as desired.
May 18, 2012 at 12:19 am
ColdCoffee (5/18/2012)
Suresh B. (5/18/2012)
Prasad.N (5/18/2012)
hi all,i have doubt regarding select statement, i have one table with 46 columns , i want select only 42 columns.but iam not like to write
select col1,col2...........col42 from mytable.
any another solution for that...............?, plz help me.
regards
prasad
You can create a view for this requirement.
Even for the view you will still have to type all the 42 columns.
That is true.
Assumed that the OP has many queries to write and wants to avoid typing the 42 column names again and again 🙂
My assumption may be wrong....
May 18, 2012 at 3:40 am
If this is just a case of wanting to reduce the amount of typing, you can get SSMS to do most of the work for you...
If you expand a table and drag the "Columns" node to a query window, it will give you a nice comma delimited list of all the columns in the table. You can then delete the few columns you don't want in your select statement.
May 18, 2012 at 4:51 am
Ian Scarlett (5/18/2012)
If this is just a case of wanting to reduce the amount of typing, you can get SSMS to do most of the work for you...If you expand a table and drag the "Columns" node to a query window, it will give you a nice comma delimited list of all the columns in the table. You can then delete the few columns you don't want in your select statement.
+1
June 21, 2012 at 1:15 am
hi all ,
Regarding above issue I got a answer
declare @var1 varchar(5000)
declare @table table (ColNames varchar(5000))
insert into @table
----here i doesn't want COLUMN45,COLUMN46,COLUMN35(you need to write which columns doesn't want)
select stuff((select ','+column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TABLENAME'
and COLUMN_NAME not in('COLUMN45','COLUMN46','COLUMN35') for xml path('')),1,1,'')
select @var1='select '+ColNames+' from TABLENAME' from @table
exec (@var1)
thanks
Prasad.N
Hyderabad-India.
June 21, 2012 at 8:45 am
Prasad.N (6/21/2012)
hi all ,Regarding above issue I got a answer
declare @var1 varchar(5000)
declare @table table (ColNames varchar(5000))
insert into @table
----here i doesn't want COLUMN45,COLUMN46,COLUMN35(you need to write which columns doesn't want)
select stuff((select ','+column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TABLENAME'
and COLUMN_NAME not in('COLUMN45','COLUMN46','COLUMN35') for xml path('')),1,1,'')
select @var1='select '+ColNames+' from TABLENAME' from @table
exec (@var1)
thanks
That works but it is a lot more difficult than just dragging the columns into the SSMS window like Ian explained above. 😉
_______________________________________________________________
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/
June 21, 2012 at 9:02 am
Prasad.N (6/21/2012)
hi all ,Regarding above issue I got a answer
declare @var1 varchar(5000)
declare @table table (ColNames varchar(5000))
insert into @table
----here i doesn't want COLUMN45,COLUMN46,COLUMN35(you need to write which columns doesn't want)
select stuff((select ','+column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TABLENAME'
and COLUMN_NAME not in('COLUMN45','COLUMN46','COLUMN35') for xml path('')),1,1,'')
select @var1='select '+ColNames+' from TABLENAME' from @table
exec (@var1)
thanks
So... Was this a homework question or were you just curious. I ask, because I would never use this in my code as it is confusing as heck and accomplishes nothing of value.
Jared
CE - Microsoft
June 21, 2012 at 9:07 am
Though it could be a case where you don't know the columns you do want but definitely want to exclude certain columns.
Like excluding all transaction audit columns from any number of different tables.
I'm not advocating its use mind you, just providing one potential use case.
June 21, 2012 at 9:21 am
JeffRush (6/21/2012)
Though it could be a case where you don't know the columns you do want but definitely want to exclude certain columns.Like excluding all transaction audit columns from any number of different tables.
I'm not advocating its use mind you, just providing one potential use case.
In that case, I would still find it "simpler" to generate a select statement and just comment out the columns not wanted.
Right-click on table, script table AS, SELECT to, new query editor window:
SELECT [column1]
,[column2]
-- ,[column3]
,[column4]
-- ,[column5]
,[column6]
FROM [database].[schema].
GO No needless code, less typing on my part, and much less confusing to anyone who has to go back and look at what I did and why I did it.
Jared
CE - Microsoft
June 21, 2012 at 9:34 am
You are tasked with pulling out the data across 50 tables. They all contain any number of columns. You want all of it but know for a fact you don't want CreatedDate, CreatedByUserID, UpdatedDate, UpdatedByUserID, as an example.
An edge case certainly but generating it via that code would be more effective than manually commenting it out for 50 tables.
June 21, 2012 at 9:44 am
JeffRush (6/21/2012)
You are tasked with pulling out the data across 50 tables. They all contain any number of columns. You want all of it but know for a fact you don't want CreatedDate, CreatedByUserID, UpdatedDate, UpdatedByUserID, as an example.An edge case certainly but generating it via that code would be more effective than manually commenting it out for 50 tables.
It is going to be very hard to convince me that this approach will ever be a good idea. In the case mentioned above, what happens when a column is added to a table definition? There is a reason we don't use SELECT * and we always explicitly state what we want returned in good development; because we will always know what is returned and can handle that. We cannot handle knowing what is NOT returned. To use this as a generation tool is one thing. However, this is clearly not as it is executing the statement right away. IMHO, to insert this into a production environment is a bad idea.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply