CODEWITHSUNDEEP
sqlsql-serverviewinsert-into
YJZ
YJZ

Reputation: 4204

use INSERT inside definition of VIEW: CREATE VIEW AS INSERT INTO

If I want to do something relatively complicated - something usually done by a stored procedure. Is it possible to make it automatic using a VIEW?

My specific case:

I want output table = input table A + some rows input table B. In a stored procedure, I can make a copy of table A and then INSERT INTO it, but it's not allowed in a view.

Simplified example:

input table is [test_album], and output table = input table + singer Prince.

--create test data
IF OBJECT_ID('[dbo].[test_album]', 'U') IS NOT NULL 
    DROP TABLE [dbo].[test_album] 

CREATE TABLE [test_album] ( 
    id int not null identity(1, 1) primary key, 
    singer VARCHAR(50) NULL, 
    album_title VARCHAR(100) NULL 
    ) 

INSERT INTO [test_album] (singer, album_title) 
    VALUES ('Adale', '19'), 
        ('Michael Jaskson', 'Thriller')

--this can be executed as sql code or in stored proc
SELECT *
INTO [result_table]
FROM [test_album]

INSERT INTO [result_table] ([singer])
    VALUES ('Prince') 

select *
from [result_table]
--id    singer  album_title
--1 Adale   19
--2 Michael Jaskson Thriller
--3 Prince  NULL
----as expected

But I can do this INSERT INTO inside a view.

Real-life case:

additional singers are in a table [extra_singers]

[test_album] may have many other columns (or schema may change) so it's ideal not to type all column names in the code.

--create test data
IF OBJECT_ID('[dbo].[test_album]', 'U') IS NOT NULL 
    DROP TABLE [dbo].[test_album] 
IF OBJECT_ID('[dbo].[extra_singers]', 'U') IS NOT NULL 
    DROP TABLE [dbo].[extra_singers] 
IF OBJECT_ID('[dbo].[result_table]', 'U') IS NOT NULL 
    DROP TABLE [dbo].[result_table]         

CREATE TABLE [test_album] ( 
    id int not null identity(1, 1) primary key, 
    singer VARCHAR(50) NULL, 
    album_title VARCHAR(100) NULL,
    many_other_columns VARCHAR(100) NULL
    ) 

INSERT INTO [test_album] (singer, album_title) 
    VALUES ('Adale', '19'), 
        ('Michael Jaskson', 'Thriller')


CREATE TABLE [extra_singers] ( 
    [id] int not null identity(1, 1) primary key, 
    [name] VARCHAR(50) NULL )

INSERT INTO [extra_singers] ([name]) 
    VALUES ('Prince'), 
        ('Taylor Swift')


--append [extra_singers] to [test_album]
--this can be executed as sql code or in stored proc
SELECT *
INTO [result_table]
FROM [test_album]

INSERT INTO [result_table] ([singer])
    SELECT [name]
    FROM [extra_singers]

Is there an alternative to this (that is automatic)? any help's appreciated. Thank u-

a partial solution I can think of:

create view test_view as

    select * 
    from [test_album]

    union all

    select 3 as id,
        'Prince' as singer,
        NULL as album_title

but you have to know all the column names in [test_album] and you can't let column [id] do auto-increment

Upvotes: 1

Views: 3403

Answers (1)

Xedni
Xedni

Reputation: 4695

So you may be misunderstanding what a view does, or what an insert is. A view is simply a wrapper around a single select query. It contains exactly one select statement, and nothing else. An insert permanently adds a row of data to a persisted table. The example you gave where you just union the row you want seems valid enough. And certainly if it's the same row you want every time, you would not want to be inserting (or even trying to insert) that row into the underlying table each time

This raises a couple questions though.

  • If you're always going to be unioning the same single row every time, why not jut add that row to the table?
  • If, lets say, you don't want that row in the underlying table, cool. But if it's always the same static values, why do you need to include it in the view? Can't it just be assumed it's there?
  • If it can't be assume to always be the same, you certainly don't want to be changing the VIEW body every time you need it to change. So if it is going to change and you don't want to insert it into the base table, maybe make a second table containing the values you want appended to the base table in the view. Then union the base table and the "extra values" table together instead of a single, hard coded row constructor.

Upvotes: 8

Related Questions