Inserting a table into a SQL Server view

I have done a bunch of research and tried to figure out how to do this, but everything that is suggested does not seem to work for me. I create a table using the following SQL:

CREATE VIEW view_name AS SELECT * FROM table1_name

When开发者_如何学运维 I do this if I make changes to table1_name these changes are reflected in the view (as I want). However, I later create a table table2_name and want to add it to this view in the same way so that if I add rows to the table they will be reflected in the view. So, I use a similar piece of code, (but use insert instead)

INSERT INTO view_name SELECT * FROM table2_name

However, now when I make additions to table2_name these are not reflected in the view. I am extremely new to SQL (started three days ago), so any thoughts or places I should look would be extremely appreciated.

(Note: I am using SQL Server, I don't seem to think that this makes much of a difference, but in case it does)

Thanks, SaxyTimmy

---

Maybe what you meant to do (assuming the columns are the same):

ALTER VIEW view_name
AS
    SELECT col1, col2 FROM table1_name
    UNION ALL
    SELECT col1, col2 FROM table2_name

As Joe pointed out, you don't insert data into the view - it is not persisted (unless it is indexed, and in that case you don't actually insert into the view either).

If you want to update the view for new tables, you can do something like this. I'm assuming you're on SQL Server 2005 or better - if your school is teaching you SQL Server 2000, shame on them. I'm also assuming a couple of other things... your view does not contain a trailing statement terminator (about the only time I'd ever advocate leaving it out) and that you don't have all kinds of nonsense in comments before the CREATE VIEW command.

CREATE PROCEDURE dbo.AddTableToView
    @view      SYSNAME,
    @new_table SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    SELECT @sql = [definition] FROM sys.sql_modules
        WHERE [object_id] = OBJECT_ID(@view);

    SELECT @sql = STUFF(@sql, CHARINDEX('CREATE VIEW', @sql), 6, 'ALTER')
        + 'UNION ALL
           SELECT col1, col2 FROM ' + @new_table;

    EXEC sp_executeSQL @sql;
END
GO

But as I suggested in the comments, this really isn't the way you wanted to go, and I suspect your professor will feel the same way.

---

You cannot insert data into a view in the way you think you can. When you run your insert statement, you are actually inserting the data into the base table (table1_name) that the view references. You are not creating some kind of link between the view and the rows of table2_name.

---

I think that you are misunderstanding what exactly a view is. A view is basically just a stored query. You don't actually insert rows into a view. Although you can have an updateable view, it is inserting rows into the underlying table.

If you want to add table2_name to the view then you need to change that view definition. If the columns exactly match table1_name then you can do something like:

ALTER VIEW view_name
AS
    SELECT
        *
    FROM
        table1_name
    UNION ALL
    SELECT
        *
    FROM
        table2_name

Alternatively, you can DROP and CREATE the view with the new definition instead of using the ALTER syntax.

Also, when you say, "if I make changes to table1_name" do you mean inserting rows or adding columns?

---

You cannot insert data into a view. Your insert inserts data into the table1_name table. (See Updatable and Insertable Views)

If you want data from both table1_name and table2_name then you have to have both table1_name and table2_name in the select based on which the view is created.

You can use either UNION* for that (if both the tables you query from have the same no/type/set of columns):

CREATE VIEW view_name AS
SELECT * FROM table1_name
UNION
SELECT * FROM table2_name

This requires that table1_name and table2_name have the same number and types of columns in the same order

Or you can join with the other table and get all the columns (or a subset of them):

CREATE VIEW view_name AS
SELECT * 
FROM table1_name join table2_name on some_column

*UNION removes the duplicates between the two select queries. UNION ALL is a better option if you are sure that the data from both tables are mutually exclusive, or if you are ok with the duplicate rows. UNION ALL performs better.