SQL Server FAQ - Inserting Data into a View

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

Can You Insert Data into a View?

Can you insert data into a view? The answer is no.

But if the question is "Can you insert data into the underlying table through view?" The answer is then yes. SQL Server will allow you to insert data into the underlying table through a view with a condition:

The insert columns must be limited to columns of a single underlying table.

The tutorial exercise below shows you how to insert data into a underlying table through a view:

USE FyiCenterData;
GO

ALTER VIEW fyi_links_top AS
   SELECT TOP 3 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

INSERT INTO fyi_links_top 
   VALUES(100001, 1000001, 'dba.fyicenter.com');
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
100001 1000001 dba.fyicenter.com
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url               notes counts      created
------ ----------------- ----- ----------- ----------
100001 dba.fyicenter.com NULL  1000001     2007-05-19

(Continued on next topic...)