Transfer data from one database to another dynamically

Question

Thank you for helping me with the problem of transferring data between 3 Databases (same server).

I would like to edit the question

Suppose I have 3 Databases: Data1 and Data2 and Data3.

And in these data there are the same tables and columns.

Now I use this command and it works ok

    CREATE PROCEDURE [dbo].[copy]
        @id_id INT,
        @namecreate Nvarchar(50),
        @txtdataname sysname
    AS BEGIN
    
    DECLARE @InsertedRows TABLE (id INT)
    
    --Insert Master
    insert into [Data2].[dbo].[Table1] (cot1, cot2, cot3, cot4, namecreate) 
       
    OUTPUT inserted.id        
    INTO @InsertedRows
    
    SELECT cot1, cot2, cot3, cot4, @namecreate From Table1 WHERE (id = @id_id)                      
    
    --Insert Detail
    insert into [Data2].[dbo].[Table2] (id, cot1, cot2, cot3, cot4) 
    SELECT (SELECT TOP (1) id FROM @InsertedRows), cot1, cot2, cot3, cot4 From Table2 WHERE (id = @id_id)
    
END

I want to change that Data2 place to a dynamic passed in @txtdataname Because I tried to write it like this, I got an error

CREATE PROCEDURE [dbo].[copy]
        @id_id INT,
        @namecreate Nvarchar(50),
        @txtdataname sysname
    AS BEGIN
    
    DECLARE @InsertedRows TABLE (id INT)
    
    --Insert Master
    insert into [@txtdataname].[dbo].[Table1] (cot1, cot2, cot3, cot4, namecreate) 
       
    OUTPUT inserted.id        
    INTO @InsertedRows
    
    SELECT cot1, cot2, cot3, cot4, @namecreate From Table1 WHERE (id = @id_id)                      
    
    --Insert Detail
    insert into [@txtdataname].[dbo].[Table2] (id, cot1, cot2, cot3, cot4) 
    SELECT (SELECT TOP (1) id FROM @InsertedRows), cot1, cot2, cot3, cot4 From Table2 WHERE (id = @id_id)
    
END

You can't substitute a table name with a variable. You will need to use dynamic sql [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) to do that — Squirrel, Jan 13 '22 at 04:34
by the way, the title said `from one server to another` but you are actually `transferring data between 3 Databases (same server)` — Squirrel, Jan 13 '22 at 04:36
Object names are unicode and have a specific length - use [sysname](https://stackoverflow.com/questions/5720212/what-is-sysname-data-type-in-sql-server). And just stop using the terrible practice of adding "type" to a variable name. — SMor, Jan 13 '22 at 12:37

score 1 · Answer 1 · answered Jan 13 '22 at 04:54

You cannot use a variable name as part of an object reference. You would need to use dynamic SQL for this.

CREATE OR ALTER PROCEDURE [dbo].[copy]
  @id_id INT,
  @txtdataname sysname
AS

DECLARE @sql nvarchar(max) = '
insert into ' + QUOTENAME(@txtdataname) + '.[dbo].[Table1] (cot1, cot2, cot3, cot4)
    SELECT cot1, cot2, cot3, cot4
    From Table1
    WHERE (id = @id_id);
';

EXEC sp_executesql @sql,
  N'@id_id int',
  @id_id = @id_id;

Note the following:

Use of QUOTENAME to correctly escape the database name
Use of nvarchar(max) to store the dynamic SQL
Use of sp_executesql to pass the parameter @id_id all the way through
Object names should always be stored as sysname
Do not use sp_ as a procedure prefix, it's reserved for system procedures

Transfer data from one database to another dynamically

1 Answers1