Thank you for helping me with the problem of transferring data between 3 Databases (same server). 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].[sp_copy]
    @id_id INT,
    @txtdataname varchar(128)
AS BEGIN
insert into [Data2].[dbo].[Table1] (cot1, cot2, cot3, cot4) 
    SELECT cot1, cot2, cot3, cot4 From Table1 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].[sp_copy]
@id_id INT,
@txtdataname varchar(128)    
AS BEGIN
insert into [@txtdataname ].[dbo].[Table1] (cot1, cot2, cot3, cot4) 
    SELECT cot1, cot2, cot3, cot4 From Table1 WHERE (id = @id_id)                     
END

CodePudding user response：

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

CodePudding user response：

Thanks for your support. I would like to correct my question. Because my reality is a bit different (I'm sorry to bother you)

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 need to add the namecreate parameter and get the newly generated id to insert into the details table Hope you help me once again.