February 7, 2012 at 3:14 pm
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.
A1A2A1B1
BeveragesMeat/PoultryBeveragesAlice Mutton
BeveragesCondimentsBeveragesAlice Mutton
BeveragesMeat/PoultryBeveragesAniseed Syrup
BeveragesCondimentsBeveragesAniseed Syrup
February 7, 2012 at 3:17 pm
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
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
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