Post reply

Create a view from tables having similar column names

  • February 7, 2012 at 3:14 pm

    #254971

    I'm trying to create a view which selects from tables which have same column names. I can specify alias name for column with silmilar name in the select statement and can create the view. I was wondering if there was a way to dynamically add the tablename+column name as an alias

    create table A

    (A1 varchar(30),

    A2 varchar(30))

    create table B

    (A1 varchar(30),

    B1 varchar(30))

    insert into A values('Beverages','Meat/Poultry')

    insert into A values('Beverages','Condiments')

    insert into B values('Beverages','Alice Mutton')

    insert into B values('Beverages','Aniseed Syrup')

    select * from A,B

    create view ABTest as select A.*,B.* from A,B

    Error :Msg 4506, Level 16, State 1, Procedure ABTest, Line 1

    Column names in each view or function must be unique. Column name 'A1' in view or function 'ABTest' is specified more than once.

    Select statement runs fine.It will be nice to add table name + columnname. I'm not sure if this possible, but I figured I will ask.

    A1 A2 A1 B1

    Beverages Meat/Poultry Beverages Alice Mutton

    Beverages Condiments Beverages Alice Mutton

    Beverages Meat/Poultry Beverages Aniseed Syrup

    Beverages Condiments Beverages Aniseed Syrup

  • February 7, 2012 at 3:17 pm

    #1444175

    explicitly name your columns and avoid the use of Select *. Also, columns can be aliased to a new column name.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 7, 2012 at 3:23 pm

    #1444183

    This is fairly small sample table, I'm dealing with 30 - 40 tables having at least 3-4 similar columns . Also, I'm thinking by doing * , I don't have to worry in modifying the view definition if a new column gets added to the table.

  • February 7, 2012 at 3:52 pm

    #1444200

    Oh but you do. You should avoid using shorthand like that. Explicitly define your columns is the better route. If you don't then you can't add columns from different tables that are the same name. You won't be able to use those tables at all - if you insist on using Select *.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply