Run insert on N databases, and return name of databases where insert occurred

  • Hi all,

    I have two insertions I'd like to make, into identically named tables, against multiple databases.  Three rules:

    1) The insertions must happen in the order below - the second cannot start until the first has finished;

    2) If an insertion DOES occur, the inserted database name(s) must show in the result set;

    3) If no insertions occur across all databases, to return a specific error message ("No insertions made, username likely incorrect, check spelling".

    Here's what I've got so far....

    IF @dbname in ('DB1', 'DB2', 'DB3') --(list of databases)

    EXECUTE ('use ['+@dbname+']; --I think I've got this part right

    --Declare Necessary Variables (not sure if the variables need to be declared on each iteration or can just be declared once at the top)

    Declare @CopyUser Varchar(10) = (Select yada yada...--

    Declare @UserOp Varchar(10)

    Insert into dbo.Valid_Emp (Op_Ref, Emp_Ref)

    -- (Values)

    Select @UserOp, Emp_Ref

    From dbo.Valid_Emp

    Where Operator_Ref = @CopyUser

    Insert into dbo.Op_Groups (Group_Ref, Op_Ref)

    -- (Values)

    Select Group_Ref, @UserOp

    From dbo.Op_Groups

    Where Op_Ref= @CopyUser

     

    Thanks in advance

    • This topic was modified 5 days, 17 hours ago by  JaybeeSQL.
    • This topic was modified 5 days, 17 hours ago by  JaybeeSQL.
  • Use sp_ForEachDB to loop over the DBs, and @@ROWCOUNT to collect the number of records affected/inserted?

    I'd just create a counter variable and increment it after each insert. Then if you get to the end of your databases, you can fork the code to do different things depending on whether there were any records inserted.

  • DECLARE @CopyUser varchar(30)
    DECLARE @Dbnames varchar(8000)
    DECLARE @Sql varchar(8000)
    DECLARE @UserOp varchar(30)

    SET @Dbnames = 'DB1,DB2,DB3' --<<--!!set these values before running!!--<<--
    SET @CopyUser = 'User1' --<<--!!set these values before running!!--<<--
    SET @UserOp = 'User2' --<<--!!set these values before running!!--<<--

    IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL
    DROP TABLE #results
    CREATE TABLE #results ( row_counter int NULL );

    SET @Sql = '
    DECLARE @dbnames varchar(8000) = '',' + @Dbnames + ','';
    IF CHARINDEX('',?,'', @Dbnames) = 0
    RETURN;
    USE [?];
    DECLARE @CopyUser varchar(30) = ''' + @CopyUser + ''';
    DECLARE @rowcount int = 0;
    DECLARE @UserOp varchar(30) = ''' + @UserOp + ''';

    INSERT INTO dbo.Valid_Emp (Op_Ref, Emp_Ref)
    SELECT @UserOp, Emp_Ref
    FROM dbo.Valid_Emp
    WHERE Operator_Ref = @CopyUser
    SET @rowcount = @@ROWCOUNT

    INSERT INTO dbo.Op_Groups (Group_Ref, Op_Ref)
    SELECT Group_Ref, @UserOp
    FROM dbo.Op_Groups
    WHERE Op_Ref = @CopyUser
    SET @rowcount = @rowcount + @@ROWCOUNT

    IF @rowcount > 0
    BEGIN
    SELECT ''?'' AS Dbname, @rowcount AS total_inserts_done
    INSERT INTO #results VALUES(@rowcount)
    END /*IF*/'

    PRINT @sql

    EXEC sp_MSforeachdb @Sql, @postcommand = N'IF NOT EXISTS(SELECT 1 FROM #results) SELECT ''No insertions made, username(s) likely incorrect, check spelling.'' AS error_message'

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • That is certainly an impressive script 🙂

    Now I'm guessing that the Set SQL has to be syntactically correct, when I altered the column names during pre-prod, I noticed a few errors, so if you  comment out everything above ...

    DECLARE @dbnames varchar(8000) = '',' + @Dbnames + ','';

    And below ...

    END /*IF*/'

    The following errors appear:

    DECLARE @dbnames varchar(8000) = '',' + @Dbnames + ','';  --"expecting variable"

    IF CHARINDEX('',?,'', @Dbnames) = 0 --"The charindex function requires 2-3 arguments"

    RETURN;

    USE [?]; --"Could not locate entry in sysdatabases..." presumably this should be @Dbnames ?

    Also, any way to return the name of each db that gets an insertion?

    Thanks Scott 🙂

  • I executed the script on an instance with 100s of dbs and it worked fine.  Of course I got the "No insertions made..." message since I didn't have dbs named DB1/DB2/DB3.

    The script returns a result with for every db affected with the db name and the count of the total rows inserted.

    As for commenting everything above and below the script out, the script depends on: the local variables (@...) you supply values; and ? being replaced with the db name by the sp_MSforeachdb script.

    Do you see any errors in the script when it prints out before being EXECed?

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • .

    • This reply was modified 4 days, 19 hours ago by  JaybeeSQL.
    • This reply was modified 4 days, 6 hours ago by  JaybeeSQL.
  • Verify that variable @CopyFromUser includes a valid value.

    Add a PRINT statement in the script.  Existing code:

    ...

    From dbo.Operator

    Where Operator_Ref = @CopyFromUser

    SET @rowcount = @@ROWCOUNT

    ...

    so that you can verify how many INSERTs were done to that table; new code:

    ...

    From dbo.Operator

    Where Operator_Ref = @CopyFromUser

    SET @rowcount = @@ROWCOUNT

    PRINT ''? Operator rows INSERTed = '' + CAST(@rowcount AS varchar(10))

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • .

    • This reply was modified 4 days, 18 hours ago by  JaybeeSQL.
    • This reply was modified 4 days, 6 hours ago by  JaybeeSQL.
  • .

    • This reply was modified 4 days, 17 hours ago by  JaybeeSQL.
    • This reply was modified 4 days, 6 hours ago by  JaybeeSQL.
  • The values go into the variables at the top.

    You'll note that I declared the variables first, then SET them all together.  I personally think it's much more confusing to mix declarations and setting, FWIW.

    Note, just to be sure, that the quotes in the code added are two single quotes '' , not a single double quote ".

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • .

    • This reply was modified 4 days, 14 hours ago by  JaybeeSQL.
    • This reply was modified 4 days, 6 hours ago by  JaybeeSQL.
  • So, JaybeeSQL... enquiring minds want to know... why have you essentially deleted several posts with no explanation?  You made the lessons taught in each post that follows one of your now empty posts pretty useless to the general public.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply