Incorrect syntax near the keyword 'table'.

Question

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,

Incorrect syntax near the keyword '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.

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

Answer 1

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.

Answer 2

read this http://support.microsoft.com/kb/555375

Answer 3

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

Must declare the scalar variable "@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

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

Answer 4

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.