January 13, 2022 at 7:03 pm
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
January 13, 2022 at 7:26 pm
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.
January 13, 2022 at 9:22 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2022 at 1:09 pm
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 🙂
January 14, 2022 at 4:45 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2022 at 6:07 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2022 at 8:10 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2022 at 4:53 pm
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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply