0

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)
2
  • 1
    Are you trying to put INSERT INTO [dbo].[Acc-Faculty-History] inside of your view?
    – J.D.
    Feb 16 at 18:40
  • I need to insert the records for the last Run Date first, then open the view to see changes. Does the insert have to go in a stored procedure or script task in SSIS?
    – ERPISE
    Feb 16 at 18:44

1 Answer 1

2

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'))
2
  • Thanks, so View is only for View
    – ERPISE
    Feb 16 at 19:01
  • @ERPISE Correct, a View is only for DQL statements, generally SELECT only statements; and cannot be used for storing queries that modify data (such as INSERTS or UPDATES).
    – J.D.
    Feb 16 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.