Pass table and database server to stored procedure

Question

I have two database instances and need to transfer data between them. I want to do it in a stored procedure. Is it possible to pass the target table (and database instance) as a parameter to the stored procedure?

I got this working:

INSERT INTO [SERVER1].[MYSCHEMA1].[TargetTable]
    ([ID], [Timestamp])
SELECT 
    [ID], [Timestamp]
FROM [SERVER2].[MYSCHEMA2].[SourceTable]
WHERE ID= @SomeId

But I want to do something like this:

CREATE PROCEDURE spDoStuff
    -- Add the parameters for the stored procedure here
     @id uniqueidentifier,
     @server nvarchar(100),
     @table nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [SERVER1].[MYSCHEMA1].[TargetTable]
        ([ID], [Timestamp])
    SELECT 
        [ID], [Timestamp]
    FROM @server.@table.[SourceTable]
    WHERE ID= @id
END

I know it can be done with dynamic SQL, but I would like to avoid it if possible. I've also read about TVP, but I'm not sure that this really is necessary as it would require me to design the table in code as well. This will be called from Entity Framework Core 2.2.

Answer 1

For completion, this is how I would write your SP:

CREATE PROCEDURE spDoStuff
    -- Add the parameters for the stored procedure here
     @id uniqueidentifier,
     @server sysname,
     @table sysname
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(MAX);
    SET @SQL = N'INSERT INTO [SERVER1].[MYSCHEMA1].[TargetTable]' + NCHAR(13) + NCHAR(10) +
               N'    ([ID], [Timestamp])' + NCHAR(13) + NCHAR(10) +
               N'SELECT [ID], [Timestamp]' + NCHAR(13) + NCHAR(10) +
               N'FROM ' + QUOTENAME(@server) + N'.' + QUOTENAME(@table) + N'.SourceTable' + NCHAR(13) + NCHAR(10) + --Seems Odd should @Table actually be @database?
               N'WHERE ID = @ID;';

    EXEC sp_executesql @SQL, N'@id uniqueidentifier', @id = @id;
END