SQL Server – How to Use Dynamic SQL for INSERT Between Databases

dynamic-sqlsql server

I need to copy a record from a Database DBa, Table Tbl1 to Database DBx, Table Tbl1 and get the Scope Identity inside the same SQL Server 2005 server

I have already checked this:
How to Dynamically change the database using TSQL

but my issue is more complicated.

I do want just to execute something in the DBx being inside DBa.
I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back.

The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well?

Example code:

CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100)
    AS

    DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82)

    SELECT @group_name = group_name ,
    @arrival = arrival,
    @departure = departure,
    @contact_id = contact_id
    FROM grp
    WHERE group_id = @group_id


    DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql',
            @sql  nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id) 
SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables?

        EXEC @exec @sql;
        go

Get all variables to string and put CONVERT inside the Dynamic SQL?
I hope I can find another solution because my table is around 300 columns 🙁

Best Answer

Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:

CREATE PROCEDURE dbo.pr_consolidation_copy_group (
    @group_id numeric(10,0),
    @database_to varchar(100)
)
AS
SET NOCOUNT ON;

DECLARE @group_name char(100)
       ,@arrival datetime
       ,@departure datetime
       ,@contact_id char(82);

 SELECT @group_name = group_name ,
        @arrival = arrival,
        @departure = departure,
        @contact_id = contact_id
   FROM grp
  WHERE group_id = @group_id;

DECLARE @sql_cmd NVARCHAR(MAX);
    SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
                 + 'INSERT grp(group_name, arrival, departure, contact_id)'
                 + 'SELECT @group_name, @arrival, @departure, @contact_id;'
                 + 'SET @id = SCOPE_IDENTITY()';

DECLARE @params NVARCHAR(MAX);
    SET @params = '@group_name CHAR(100),'
                + '@arrival DATETIME,'
                + '@departure DATETIME,'
                + '@contact_id CHAR(82),'
                + '@id INT OUTPUT';

DECLARE @id INT;

   EXEC sp_executeSql @stmt = @sql_cmd
                    , @params = @params
                    , @group_name = @group_name
                    , @arrival = @arrival
                    , @departure = @departure
                    , @contact_id = @contact_id
                    , @id = @id OUTPUT; 

-- @id is now available to use
END

You can find more information about outputting a variable in the documentation for sp_executeSql.

Related Question