Pass a table name to INSERT INTO tablename from outside or a variable?

I have more tables with great number of entries in production that I want to move to history database.

I have tried to use this query that works fine but I don't know if there is a possibility to give a name of a tables from outside. I don't want to write this query for every database and table.

INSERT INTO [database.history].dbo.tablename
SELECT *
FROM [database.live].dbo.table

DELETE FROM [database.history].dbo.tablename

I want to give names of databases and tables from the outside. I use dapper ORM. Is there any solution to use something similar to other types? For example.

DECLARE @ProductionTableName nvarchar(250) = '[database.live].dbo.table'

My databases are on the same server.

My queries are in files and I my call looks something like.

string query = queryFile;
var param = new {Parameter1= stringValue};
var result = Connection.Query<long>(query, param);

Solution

You can't parameterize identifiers in SQL. What you can do is use dynamic SQL.
Please note, however, that using dynamic SQL might be dangerous unless done correctly.

The basic rule is actually very simple - You must parameterize what you can, and white-list what you can't.
For more details, read my blog post The do’s and don’ts of dynamic SQL for SQL Server.

Also, since you're moving records from one table to another you should use a transaction, otherwise, if your delete statement fails with an error, the insert statement will not be rolled back, and you will end up with the rows that lives in both databases.
For more information, read Understanding Cross-Database Transactions in SQL Server over on red-gate.

Please note that if the history database is on a different server then the live database, you will need a distributed transaction.

A working example would be something like this:

DECLARE @TableName sysname = 'TableName',
        @LineBreak nchar(2) = NCHAR(13) + NCHAR(10),
        @Sql nvarchar(4000);

IF EXISTS(
    SELECT 1 
    FROM [database.live].Information_schema.Tables
    WHERE Table_Name = @TableName
)
BEGIN

    SET @SQL = 'BEGIN TRANSACTION' + @LineBreak + 
    'BEGIN TRY' + @LineBreak + 
    'INSERT INTO [database.history].dbo.' + @TableName + @LineBreak + 
    'SELECT *' + @LineBreak + 
    'FROM [database.live].dbo.' + @TableName + @LineBreak +

    'DELETE FROM [database.history].dbo.' + @TableName + 
    'COMMIT TRANSACTION' + @LineBreak + 
    'END TRY' + @LineBreak + 
    'BEGIN CATCH' + @LineBreak + 
    'IF @@TRANCOUNT > 0' + @LineBreak + 
    '    ROLLBACK TRANSACTION' + @LineBreak + 
    'END CATCH' + @LineBreak

    -- When working with dynamic SQL, Print is your best friend.
    PRINT @SQL
    -- Once you've verified that the SQL looks ok, you can unremark the EXEC statemet.
    --EXEC sp_ExecuteSql @SQL
END

Please note, however, that it's considered bad practice to use an insert statement without specifying the columns list, as well as using select *.
To add that to your query, you can query sys.columns to get the columns of the table you want.

I've also noticed that the code in the question doesn't contain a where clause - meaning the entire content of the row will be moved from live database to history database.