Insert into table from temp table using dynamic SQL; Error: Arithmetic overflow error converting varchar to data type numeric

Question

I'm working on a SQL Server stored procedure.

This is the part of code which works:

INSERT INTO [MYBASE].dbo.[DatabaseName]
    ([Employee No_], [From Date], [To Date], [Description], [Quantity],[First Name], [Last Name])   
    SELECT 
        EmployeeNo, FromDate, ToDate, Description, Quantity, 
        FirstName, LastName 
    FROM 
        #User_Data  

Now, I need to change this part:

INSERT INTO [MYBASE].dbo.[DatabaseName]

where DatabaseName will be variable.

I've tried with this:

CREATE TABLE #User_Data 
(
    User_id INTEGER,
    FirstName VARCHAR(30),
    LastName VARCHAR(30),
    EmployeeNo VARCHAR(20),
    Description VARCHAR(50),
    FromDate DATETIME,
    ToDate DATETIME 
)

INSERT INTO #User_Data
    SELECT u.fname, u.lname, u.employee_no,  ... (this works)

DECLARE @InsertStatement VARCHAR(MAX)

SET @InsertStatement =
     'INSERT INTO [MYBASE].dbo.[' + @DatabaseName + ']
               ([Employee No_], [From Date], [To Date], [Description],
                [Quantity], [First Name], [Last Name])   
          SELECT 
              EmployeeNo, FromDate, ToDate, Description,
              Quantity, FirstName, LastName 
          FROM 
              #User_Data'   

    EXEC(@InsertStatement)

but it does not work. This is the error I'm getting:

Arithmetic overflow error converting varchar to data type numeric.

Please help.

Answer 1

I've been doing this with Synonyms to avoid having to write within quotes:

Declare @MyDB as nvarchar(100) = 'myDBName'

if exists(select * from sys.synonyms where name = N'TableinMyDB') drop synonym [TableinMyDB];
exec('CREATE SYNONYM [TableinMyDB] FOR [' + @MyDB + '].dbo.[myTableName];');

 INSERT INTO [TableinMyDB]
               (
                [Employee No_]
               ,[From Date]
               ,[To Date]
               ,[Description]
               ,[Quantity]
               ,[First Name]
               ,[Last Name]
               )   

        SELECT 
               EmployeeNo 
               ,FromDate 
               ,ToDate
               ,Description
               ,Quantity
               ,FirstName
               ,LastName 
                FROM #User_Data