INSERT to a table from a database to other (same SQL Server) using Dynamic SQL The Next CEO of Stack OverflowHow to Dynamically change the database using TSQLHow the indexes are managed?Script to query multiple instancesHow to avoid using variables in WHERE clauseOracle GoldenGate add trandata errorsCreate A View Using Dynamic SqlDynamic Pivot SQL tableSyntax Error HelpInvestigating errors from strange queryDynamic SQL isn't updating tableHow to copy all Database from Source to Destination server along with login credentials?

Is it okay to majorly distort historical facts while writing a fiction story?

INSERT to a table from a database to other (same SQL Server) using Dynamic SQL

Recycling old answers

Why is quantifier elimination desirable for a given theory?

How do I align (1) and (2)?

How a 64-bit process virtual address space is divided in Linux?

Solving system of ODEs with extra parameter

Is this "being" usage is essential?

Unreliable Magic - Is it worth it?

Why do airplanes bank sharply to the right after air-to-air refueling?

Is it professional to write unrelated content in an almost-empty email?

Can MTA send mail via a relay without being told so?

If Nick Fury and Coulson already knew about aliens (Kree and Skrull) why did they wait until Thor's appearance to start making weapons?

Why does the flight controls check come before arming the autobrake on the A320?

How to install OpenCV on Raspbian Stretch?

Why this way of making earth uninhabitable in Interstellar?

Reference request: Grassmannian and Plucker coordinates in type B, C, D

Are police here, aren't itthey?

I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin

Should I tutor a student who I know has cheated on their homework?

Is there always a complete, orthogonal set of unitary matrices?

What flight has the highest ratio of time difference to flight time?

Domestic-to-international connection at Orlando (MCO)

Can we say or write : "No, it'sn't"?



INSERT to a table from a database to other (same SQL Server) using Dynamic SQL



The Next CEO of Stack OverflowHow to Dynamically change the database using TSQLHow the indexes are managed?Script to query multiple instancesHow to avoid using variables in WHERE clauseOracle GoldenGate add trandata errorsCreate A View Using Dynamic SqlDynamic Pivot SQL tableSyntax Error HelpInvestigating errors from strange queryDynamic SQL isn't updating tableHow to copy all Database from Source to Destination server along with login credentials?










3















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 :(










share|improve this question




























    3















    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 :(










    share|improve this question


























      3












      3








      3








      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 :(










      share|improve this question
















      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 :(







      sql-server dynamic-sql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 3 hours ago







      PanosPlat

















      asked 3 hours ago









      PanosPlatPanosPlat

      2571410




      2571410




















          1 Answer
          1






          active

          oldest

          votes


















          3














          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.






          share|improve this answer

























            Your Answer








            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "182"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233579%2finsert-to-a-table-from-a-database-to-other-same-sql-server-using-dynamic-sql%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            3














            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.






            share|improve this answer





























              3














              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.






              share|improve this answer



























                3












                3








                3







                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.






                share|improve this 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.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited 1 hour ago

























                answered 2 hours ago









                Mr.BrownstoneMr.Brownstone

                9,75232342




                9,75232342



























                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233579%2finsert-to-a-table-from-a-database-to-other-same-sql-server-using-dynamic-sql%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Can not update quote_id field of “quote_item” table magento 2Magento 2.1 - We can't remove the item. (Shopping Cart doesnt allow us to remove items before becomes empty)Add value for custom quote item attribute using REST apiREST API endpoint v1/carts/cartId/items always returns error messageCorrect way to save entries to databaseHow to remove all associated quote objects of a customer completelyMagento 2 - Save value from custom input field to quote_itemGet quote_item data using quote id and product id filter in Magento 2How to set additional data to quote_item table from controller in Magento 2?What is the purpose of additional_data column in quote_item table in magento2Set Custom Price to Quote item magento2 from controller

                    How to solve knockout JS error in Magento 2 Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?(Magento2) knockout.js:3012 Uncaught ReferenceError: Unable to process bindingUnable to process binding Knockout.js magento 2Cannot read property `scopeLabel` of undefined on Product Detail PageCan't get Customer Data on frontend in Magento 2Magento2 Order Summary - unable to process bindingKO templates are not loading in Magento 2.1 applicationgetting knockout js error magento 2Product grid not load -— Unable to process binding Knockout.js magento 2Product form not loaded in magento2Uncaught ReferenceError: Unable to process binding “if: function()return (isShowLegend()) ” magento 2

                    Nissan Patrol Зміст Перше покоління — 4W60 (1951-1960) | Друге покоління — 60 series (1960-1980) | Третє покоління (1980–2002) | Четверте покоління — Y60 (1987–1998) | П'яте покоління — Y61 (1997–2013) | Шосте покоління — Y62 (2010- ) | Посилання | Зноски | Навігаційне менюОфіційний український сайтТест-драйв Nissan Patrol 2010 7-го поколінняNissan PatrolКак мы тестировали Nissan Patrol 2016рвиправивши або дописавши її