smart work with select

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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....

  • 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.

  • 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


    Sujeet Singh

  • 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.

  • 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/

  • 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

  • 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.

  • 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

  • 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.

  • 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