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



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


  • 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 https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15 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'))




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2