CODEWITHSUNDEEP
sqlsql-viewcreate-view
Naphstor
Naphstor

Reputation: 2496

Creating view error column names must be unique

Hi I am trying to resolve an error with creating view. My view is as follows:

CREATE VIEW [dbo].[vw_Employee_Data] AS
SELECT * 
  FROM [dbo].[Employee] as emp
  inner join [dbo].[Managar] as mgr on emp.NewEmp=mgr.EmpID
GO

When I create this view I get the following error:

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

So I read here about a simple solution to specify all the column names to be selected. I checked this stackoverflow question which is somewhat similar.

If I specify each column in "SELECT", it works fine but the problem is my table employee table has more than 25 columns and Manager table has around 10 columns, which would make my query very long and not sure if there would be any performance issues.

But when I tried to shorten my query as follows, I get same duplication column error: 

CREATE VIEW [dbo].[vw_Employee_Data] AS
SELECT emp.*, [mgr].[EmpID] AS [MGREmpID], [mgr].[BaseLID] AS [MGRBaseLID], mgr.*
  FROM [dbo].[Employee] as emp
  inner join [dbo].[Managar] as mgr on emp.NewEmp=mgr.EmpID

So I wanted to know if there is another way to resolve it. I am new to SQL so not sure if I am using the tbl.* format correctly or not.

Any comments?

Upvotes: 0

Views: 6383

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Don't use *. List out all the columns:

CREATE VIEW [dbo].[vw_Employee_Data] AS
    SELECT e.*, m.EmpID AS MGREmpID, m.BaseLID AS MGRBaseLID
    FROM [dbo].Employee e INNER JOIN
         [dbo].Manager m
         ON e.NewEmp = m.EmpID;

If you have more columns from mgr, then list them and be sure they don't conflict with emp columns.

Upvotes: 2

Related Questions