View to insert records into history table and then find changes with Except query

Question

I want to insert records to a history table and show the changes from the previous day in a view. The script works, but when I try to create a view I get an error message that

incorrect syntax near insert expecting ( select or with'.

How would I do that with a view? SQL Server 2016

CREATE VIEW [dbo].[Acc-Faculty-Changes] 
AS 
INSERT INTO [dbo].[Acc-Faculty-History]

       (
       [Run Date]
      ,[ID]
      ,[Name]
      ,[First]
      ,[Last]
      ,[Email]
      ,[Department]
      ,[Phone Number]
      ,[Mobile Number]
      )

SELECT DISTINCT 

       CURRENT_TIMESTAMP [Run Date]
      ,RTRIM(ISNULL(FL.INSTRCTR_ID_NUM, SPACE(0))) as ID
      ,RTRIM(ISNULL(NM.FIRST_NAME + ' ' + NM.LAST_NAME, SPACE(0))) as Name
      ,RTRIM(ISNULL(NM.FIRST_NAME, SPACE(0))) as First
      ,RTRIM(ISNULL(NM.LAST_NAME, SPACE(0))) as Last
      ,RTRIM(ISNULL(NM.EMAIL_ADDRESS, SPACE(0))) as Email
      ,RTRIM(ISNULL(SM.DIVISION_CDE, SPACE(0))) as  Department
      ,AM.PHONE [Phone Number]
      ,NM.MOBILE_PHONE [Mobile Number]
      FROM DevPrd.dbo.FACULTY_LOAD_TABLE FL
      INNER JOIN DevPrd.dbo.YEAR_TERM_TABLE YT
      ON FL.YR_CDE = YT.YR_CDE
      AND FL.TRM_CDE = YT.TRM_CDE
      INNER JOIN DevPrd.dbo.NAME_MASTER NM
      ON NM.ID_NUM = FL.INSTRCTR_ID_NUM
      --INNER JOIN DevPrd.dbo.FACULTY_MASTER FM
      --ON NM.ID_NUM = FM.ID_NUM
      INNER JOIN DevPrd.dbo.ADDRESS_MASTER AM
      ON NM.ID_NUM = AM.ID_NUM
      --WHERE FM.ACTIVE = 'Y'
      --AND NM.CURRENT_ADDRESS <> '*LHP'
      INNER JOIN DevPrd.dbo.SECTION_MASTER SM
      ON FL.YR_CDE = SM.YR_CDE
      AND FL.TRM_CDE = SM.TRM_CDE
    
      WHERE 
    
      YT.TRM_BEGIN_DTE <= dateadd(mi, 30, FORMAT(CURRENT_TIMESTAMP, 'd') ) --set to day of course 
      AND YT.TRM_END_DTE >= dateadd(mi, 30, FORMAT(CURRENT_TIMESTAMP, 'd'))

--View change

    SELECT        ID, Name, First, Last, Email, Department, [Phone Number], [Mobile Number]
    FROM            DEVReporting.dbo.[Acc-Faculty-History]
      WHERE [Run Date] = (SELECT MAX([Run Date Top 2]) [Max Run Date]
                            FROM
                            (SELECT TOP 2 [Run Date] [Run Date Top 2]
                              FROM [DEVReporting].[dbo].[Acc-Faculty-History]
                              GROUP BY [Run Date]
                            ORDER BY [Run Date] DESC) X)
    EXCEPT
    SELECT        ID, Name, First, Last, Email, Department, [Phone Number], [Mobile Number]
    FROM            DEVReporting.dbo.[Acc-Faculty-History]
      WHERE [Run Date] = (SELECT Min([Run Date Top 2]) [Max Run Date]
                            FROM
                            (SELECT TOP 2 [Run Date] [Run Date Top 2]
                              FROM [DEVReporting].[dbo].[Acc-Faculty-History]
                              GROUP BY [Run Date]
                            ORDER BY [Run Date] DESC) X)

Answer 1

Based on your response in the comments it sounds like you were trying to use an INSERT statement inside of your View's definition. That is not allowed and therefore not proper T-SQL syntax which explains the error you received:

incorrect syntax near insert expecting ( select or with

Views are essentially just a placeholder object for a DQL (SELECT) statement. Instead, if you want this code to be re-usable, you can create a stored procedure like so:

CREATE PROCEDURE [dbo].[Acc-Faculty-Changes] AS

INSERT INTO [dbo].[Acc-Faculty-History]
(
   [Run Date]
  ,[ID]
  ,[Name]
  ,[First]
  ,[Last]
  ,[Email]
  ,[Department]
  ,[Phone Number]
  ,[Mobile Number]
)
SELECT DISTINCT
   CURRENT_TIMESTAMP [Run Date]
  ,RTRIM(ISNULL(FL.INSTRCTR_ID_NUM, SPACE(0))) as ID
  ,RTRIM(ISNULL(NM.FIRST_NAME + ' ' + NM.LAST_NAME, SPACE(0))) as Name
  ,RTRIM(ISNULL(NM.FIRST_NAME, SPACE(0))) as First
  ,RTRIM(ISNULL(NM.LAST_NAME, SPACE(0))) as Last
  ,RTRIM(ISNULL(NM.EMAIL_ADDRESS, SPACE(0))) as Email
  ,RTRIM(ISNULL(SM.DIVISION_CDE, SPACE(0))) as  Department
  ,AM.PHONE [Phone Number]
  ,NM.MOBILE_PHONE [Mobile Number]
FROM DevPrd.dbo.FACULTY_LOAD_TABLE FL
INNER JOIN DevPrd.dbo.YEAR_TERM_TABLE YT
  ON FL.YR_CDE = YT.YR_CDE
  AND FL.TRM_CDE = YT.TRM_CDE
  INNER JOIN DevPrd.dbo.NAME_MASTER NM
  ON NM.ID_NUM = FL.INSTRCTR_ID_NUM
  --INNER JOIN DevPrd.dbo.FACULTY_MASTER FM
  --ON NM.ID_NUM = FM.ID_NUM
INNER JOIN DevPrd.dbo.ADDRESS_MASTER AM
  ON NM.ID_NUM = AM.ID_NUM
  --WHERE FM.ACTIVE = 'Y'
  --AND NM.CURRENT_ADDRESS <> '*LHP'
INNER JOIN DevPrd.dbo.SECTION_MASTER SM
  ON FL.YR_CDE = SM.YR_CDE
  AND FL.TRM_CDE = SM.TRM_CDE

WHERE

  YT.TRM_BEGIN_DTE <= dateadd(mi, 30, FORMAT(CURRENT_TIMESTAMP, 'd') ) --set to day of course 
  AND YT.TRM_END_DTE >= dateadd(mi, 30, FORMAT(CURRENT_TIMESTAMP, 'd'))