Inserting a table into a SQL Server view

Question

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

Answer 1

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.