locked
Adding a new column from a table to a SQL view RRS feed

  • Question

  • Hello

    I have a SQL VIEW with col1,col2,col3. I need to add a new column to the view col4 coming from a TABLE in SQL Server.

    Can someone help me with this.

    Monday, November 2, 2015 6:29 PM

Answers

  • Select the View in the Object Explorer, right click, and select Script View as -> Alter to -> New Query Editor Window. That will Alter script to modify the view in a new window.

    Add new column and then hit F5 or execute.

    Sample to add MyNewColumn in MyView:

    ALTER VIEW MyView
    AS
    
    SELECT Col1, Col2, MyNewColumn
    FROM [ETL].[Claim] WITH (NOLOCK)

    Snapshot:


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Monday, November 2, 2015 7:18 PM

All replies

  • on what basis you want to link to the table? ie related columns

    it will like this

    CREATE VIEW ViewName
    AS
    SELECT t1.col1,t1.col2,t1.col3,t2.col4
    FROM table1 t1
    INNER JOIN table2 t2
    ON t2.Column = t1.RelatedColumn

    in case col1,col2 etc are static values you can use this

    CREATE VIEW ViewName
    AS
    SELECT <value1> AS col1,
    <value2> AS col2,
    <value3> AS col3,
    column AS col4
    FROM table


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, November 2, 2015 6:43 PM
  • I cannot create a new view I need to use the existing one and add a new col from a different table

    Thanks

    Monday, November 2, 2015 7:12 PM
  • Select the View in the Object Explorer, right click, and select Script View as -> Alter to -> New Query Editor Window. That will Alter script to modify the view in a new window.

    Add new column and then hit F5 or execute.

    Sample to add MyNewColumn in MyView:

    ALTER VIEW MyView
    AS
    
    SELECT Col1, Col2, MyNewColumn
    FROM [ETL].[Claim] WITH (NOLOCK)

    Snapshot:


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Monday, November 2, 2015 7:18 PM
  • As you mentioned you need to add a column from a different table, you need to join the table appropriately with the existing table so that you maintain the data integrity and add the column to the list as below..

    alter view <view name>
    as 
    select <a.columname>,<b.columname> from a inner join b on a.col=b.col

    Wednesday, December 2, 2015 11:55 PM