查看以將記錄插入歷史表，然後使用除查詢查找更改

我想將記錄插入歷史表並在視圖中顯示前一天的更改。該腳本有效，但是當我嘗試創建視圖時，我收到一條錯誤消息

預期插入附近的語法不正確（選擇或使用’。

我將如何做到這一點？SQL 伺服器 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'))

–查看變化

   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)

根據您在評論中的回复，聽起來您試圖在INSERTView 的定義中使用聲明。這是不允許的，因此不適合解釋您收到的錯誤的 T-SQL 語法：

插入預期附近的語法不正確（選擇或使用

視圖本質上只是 DQL ( SELECT) 語句的佔位符對象。相反，如果您希望此程式碼可重用，您可以創建一個儲存過程，如下所示：

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'))

引用自：https://dba.stackexchange.com/questions/307585