关键字“table”附近的语法不正确

I am receiving this error. What I am doing is trying to take data from one table and one db and place it into another db and table. The tables are not exactly the same. I am using a FETCH cursor, so I fetch the first row from db1 table and then place each column value into declared variables. Then I run the insert statement into db2 table and fetch the next value. It all seems to be working properly because it runs through fine but at the end I get this error,

我收到此错误。我正在做的是尝试从一个表和一个数据库中获取数据并将其放入另一个数据库和表中。表格并不完全相同。我正在使用FETCH游标,因此我从db1表中获取第一行,然后将每个列值放入声明的变量中。然后我将insert语句运行到db2表中并获取下一个值。这一切似乎都正常工作,因为它运行良好,但最后我得到这个错误,

Incorrect syntax near the keyword 'table'.

关键字“table”附近的语法不正确。

The whole transaction statement is in a TRY/CATCH with an error handling expression in the CATCH block. Other than that I don't know what causes this. Please help.

整个事务语句在TRY / CATCH中,在CATCH块中具有错误处理表达式。除此之外我不知道是什么原因引起的。请帮忙。

Here is the code

这是代码

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  BEGIN TRY
    BEGIN TRANSACTION

    --TURN OFF ITENDITY COLUMNS
    SET IDENTITY_INSERT [DB].[dbo].[TEST] ON

    --TURN OFF ALL CONSTRAINTS 
    ALTER TABLE [DB].[dbo].[TEST] NOCHECK CONSTRAINT ALL

    -- Insert statements for procedure here
    DECLARE  @ID int,
             @DT datetime,
             @PID varchar(10),
             @AREA varchar(20)


    DECLARE FETCH_TEST CURSOR FOR

    SELECT [ID]
      ,[Date]
      ,[PID]
      ,[Area]

    FROM [OLDDB].[dbo].[TEST] as db1

    OPEN FETCH_TEST;

    FETCH NEXT FROM FETCH_TEST INTO @ID, 
             @DT,
             @PID,
             @AREA


    WHILE @@FETCH_STATUS = 0
      BEGIN

             --INSTER VALUES INTO THE TABLE
            INSERT INTO [DB].[dbo].[TEST]
                       ([ID]
                       ,[DT]
                       ,[PID]
                       ,[AREA])
                  VALUES
                    (@ID, 
                     @DT,
                     @PID,
                     @AREA)


          FETCH NEXT FROM FETCH_TEST INTO 
             @ID, 
             @DT,
             @PID,
             @AREA,


     END;

    CLOSE FETCH_TEST;
    DEALLOCATE FETCH_TEST;

    -- If we reach here, success!
    COMMIT

  END TRY
  BEGIN CATCH
    -- Whoops, there was an error
    IF @@TRANCOUNT > 0
      ROLLBACK

     -- Raise an error with the details of the exception
     DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
     SELECT @ErrMsg = ERROR_MESSAGE(),
            @ErrSeverity = ERROR_SEVERITY()

     RAISERROR(@ErrMsg, @ErrSeverity, 1)
  END CATCH

  --TURN OFF ITENDITY COLUMNS
  SET IDENTITY_INSERT [DB].[dbo].[TEST] OFF

  --TURN ON ALL CONSTRAINTS 
  ALTER TABLE [DB].[dbo].[TEST] CHECK CONSTRAINT ALL

END

4 个解决方案

#1

First thing is that @TRAIN_ID is never declared in your code

首先,在您的代码中永远不会声明@TRAIN_ID

Must declare the scalar variable "@TRAIN_ID".

必须声明标量变量“@TRAIN_ID”。

second is that you do NOT need a cursor, what is wrong with a SET based operation instead? It will perform much better!! Replace the cursor part with this

第二是你不需要光标,而是基于SET的操作有什么问题呢?它会表现得更好!!用这个替换光标部分

INSERT INTO [DB].[dbo].[TEST]
                       ([ID]
                       ,[DT]
                       ,[PID]
                       ,[AREA])

SELECT [ID]
      ,[Date]
      ,[PID]
      ,[Area]
FROM [OLDDB].[dbo].[TEST] as db1

you also have an extra comma here

你还有一个额外的逗号

 FETCH NEXT FROM FETCH_TEST INTO 
             @ID, 
             @DT,
             @PID,
             @AREA,

should be

 FETCH NEXT FROM FETCH_TEST INTO 
             @ID, 
             @DT,
             @PID,
             @AREA

But like I said you don't need a cursor for this

但就像我说的那样你不需要光标

#2

And by the way, you don't need a cursor for this.

顺便说一句,你不需要光标。

INSERT INTO [DB].[dbo].[TEST]
                     ([ID]
                     ,[DT]                       
                     ,[PID]                       
                     ,[AREA])
SELECT    [ID]      
  ,[Date]      
  ,[PID]      
 ,[Area]    
FROM [OLDDB].[dbo].[TEST]

This does the same job and is faster.

这做同样的工作,速度更快。

#3

The keyword table is not useful in either selecting from one table or inserting into another. Wherever you have written it, it doesn't belong there.

关键字表在从一个表中选择或插入另一个表时没有用。无论你在哪里写它,它都不属于那里。

#4

Try to replace the 'Table' (Table Name) in the queries with '[Table]'. Since Table is already a reserved keyword, there are chances the visual studio may not allow you to establish a connection to such table names.

尝试用'[Table]'替换查询中的'Table'(表名)。由于Table已经是保留关键字,因此visual studio可能不允许您建立与此类表名的连接。

#1