August 15, 2022 at 11:26 am
Hi all, I'm looking to sharpen my SQL/Programming skills, below is a sproc I wrote earlier, it's in prod but I can't shake the feeling it's more cobbled together than professional. Looking for any and all critique as to what I could have done better!! TIA
----------------------------------------------------------------------------------------------------------------------------------
--Insert Operator Script (IOS)
--The purpose of IOS is to add new users to Tampex (known within the software as "Operators"). You will need:
-- 1) To connect this script to 10.**.**.** (Live);
-- 2) The full name of the new user, along with the name of an existing accountholder whose permissions the script will replicate,
-- and to add this information to the 4 variables immediately below.
Declare @CopyFromUserFull Varchar(20) = 'Susan Boyle' --The name of the existing user
Declare @UserForename varchar(20) = 'Cindy' --The new user's first name here
Declare @UserSurname varchar(20) = 'Crawford' --The new users surname here
Declare @UserOperator varchar(10) = 'CINDYC' --AKA Operator Reference - this is usually firstname + first letter of surname, --ie Chris Smith = CHRISS and may NOT be more than 10 characters!!
--Then just press F5. The script will return a list, in the following format, of instances where it found the existing accountholder and created your new user,
-- all you now need to do is add the password:
-- Instance Operator_Ref
-------- ------------
-- Test CABARET CCRAWFORD
----------------------------------------------------------------------------------------------------------------------------------
Declare @CopyFromUser Varchar(10) = (Select Top 1 * from
(Select Operator_Ref From [ProdServerName].[TampexLive_CABARET].[dbo].Operator Where Operator_Forename+' '+Operator_Surname = @CopyFromUserFull
Union
Select Operator_Ref From [ProdServerName].[TampexLive_SARGON].[dbo].Operator Where Operator_Forename+' '+Operator_Surname = @CopyFromUserFull
Union
Select Operator_Ref From [ProdServerName].[TampexLive_PROF].[dbo].Operator Where Operator_Forename+' '+Operator_Surname = @CopyFromUserFull
Union
Select Operator_Ref From [ProdServerName].[TampexLive_C777].[dbo].Operator Where Operator_Forename+' '+Operator_Surname = @CopyFromUserFull
Union
Select Operator_Ref From [ProdServerName].[TampexLive_ROI].[dbo].Operator Where Operator_Forename+' '+Operator_Surname = @CopyFromUserFull) As Tia )
--Begin insertions to Live Environment
Insert into [ProdServerName].[TampexLive_CABARET].[dbo].Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerName].[TampexLive_CABARET].[dbo].Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_CABARET].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerName].[TampexLive_CABARET].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_CABARET].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerName].[TampexLive_CABARET].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'BA Live' As Instance , Operator_Ref
From [ProdServerName].[TampexLive_CABARET].dbo.Operator
Where Operator_Ref = @UserOperator
--Use ProdServerName.TampexLive_C777
-- INSERT STATEMENTS
Insert into [ProdServerName].[TampexLive_C777].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerName].[TampexLive_C777].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_C777].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerName].[TampexLive_C777].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_C777].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerName].[TampexLive_C777].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'C777 Live' As Instance , Operator_Ref
From [ProdServerName].[TampexLive_C777].dbo.Operator
Where Operator_Ref = @UserOperator
--Use [ProdServerName].[TampexLive_PROF]
-- INSERT STATEMENTS
Insert into [ProdServerName].[TampexLive_PROF].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerName].[TampexLive_PROF].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_PROF].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerName].[TampexLive_PROF].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_PROF].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerName].[TampexLive_PROF].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'PROF Live' As Instance , Operator_Ref
From [ProdServerName].[TampexLive_PROF].dbo.Operator
Where Operator_Ref = @UserOperator
-- INSERT STATEMENTS
Insert into [ProdServerName].[TampexLive_SARGON].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerName].[TampexLive_SARGON].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_SARGON].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerName].[TampexLive_SARGON].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_SARGON].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerName].[TampexLive_SARGON].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'SARGON Live' As Instance , Operator_Ref
From [ProdServerName].[TampexLive_SARGON].dbo.Operator
Where Operator_Ref = @UserOperator
--Use [ProdServerName].[TampexLive_ROI].
Insert into [ProdServerName].[TampexLive_ROI].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerName].[TampexLive_ROI].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_ROI].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerName].[TampexLive_ROI].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerName].[TampexLive_ROI].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerName].[TampexLive_ROI].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'ROI Live' As Instance , Operator_Ref
From [ProdServerName].[TampexLive_ROI].dbo.Operator_Groups
Where Operator_Ref = @UserOperator
--Begin insertions to Test Environment
Insert into [ProdServerNameTest].[TampexTest_CABARET].[dbo].Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerNameTest].[TampexTest_CABARET].[dbo].Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_CABARET].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerNameTest].[TampexTest_CABARET].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_CABARET].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerNameTest].[TampexTest_CABARET].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'Test CABARET' As Instance , Operator_Ref
From [ProdServerNameTest].[TampexTest_CABARET].dbo.Operator
Where Operator_Ref = @UserOperator
--Use ProdServerNameTest.TampexTest_C777
Insert into [ProdServerNameTest].[TampexTest_C777].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerNameTest].[TampexTest_C777].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_C777].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerNameTest].[TampexTest_C777].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_C777].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerNameTest].[TampexTest_C777].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'Test C777' As Instance , Operator_Ref
From [ProdServerNameTest].[TampexTest_C777].dbo.Operator
Where Operator_Ref = @UserOperator
--Use [ProdServerNameTest].[TampexTest_PROF]
Insert into [ProdServerNameTest].[TampexTest_PROF].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerNameTest].[TampexTest_PROF].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_PROF].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerNameTest].[TampexTest_PROF].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_PROF].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerNameTest].[TampexTest_PROF].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'Test_PROF' As Instance , Operator_Ref
From [ProdServerNameTest].[TampexTest_PROF].dbo.Operator
Where Operator_Ref = @UserOperator
--Use [ProdServerNameTest].[TampexTest_SARGON].
Insert into [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_SARGON].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerNameTest].[TampexTest_SARGON].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'Test_SARGON' As Instance , Operator_Ref
From [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator
Where Operator_Ref = @UserOperator
--Use [ProdServerNameTest].[TampexTest_ROI].
Insert into [ProdServerNameTest].[TampexTest_ROI].dbo.Operator
(Operator_Ref, Operator_Forename, Operator_Surname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, Must_Enter_Password, Load_Tree_Menu_On_Login_TF, Dataview_Code,
Expired_TF, Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name,
Allow_Windows_Login_TF, Portal_Username, TampexAutomationEmailAddress)
-- (Values)
Select @UserOperator, @UserForename, @UserSurname, Multiple_Logging, Usage_Logging, Screen_Access_Mode, Flip_Fld_In_Scrn_Mod, Flip_Fld_In_Line_Mod,
Display_Buttons, Confirm_Box_Default, Confirm_Print_Dest, Allow_Alarm, Allow_Mail, 'T', Load_Tree_Menu_On_Login_TF, Dataview_Code, Expired_TF,
Report_CSV_Folder, Open_CSV_Reports, CSV_Report_Viewer_Path, Active_Directory_User_TF, Active_Directory_Login_Name, Allow_Windows_Login_TF, Portal_Username,
TampexAutomationEmailAddress
From [ProdServerNameTest].[TampexTest_ROI].dbo.Operator
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_ROI].dbo.Valid_Employers (Operator_Ref, Employer_Ref)
-- (Values)
Select @UserOperator, Employer_Ref
From [ProdServerNameTest].[TampexTest_ROI].dbo.Valid_Employers
Where Operator_Ref = @CopyFromUser
Insert into [ProdServerNameTest].[TampexTest_ROI].dbo.Operator_Groups (Group_Ref, Operator_Ref)
-- (Values)
Select Group_Ref, @UserOperator
From [ProdServerNameTest].[TampexTest_ROI].dbo.Operator_Groups
Where Operator_Ref = @CopyFromUser
Select 'Test_ROI' As Instance , Operator_Ref
From [ProdServerNameTest].[TampexTest_ROI].dbo.Operator_Groups
Where Operator_Ref = @UserOperator
August 15, 2022 at 1:29 pm
Is there any chance that you can post a properly formatted version inside a code block. It is unreadable as it stands.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 15, 2022 at 3:07 pm
It initial glance it appears you assume all users have different names. Does @UserOperator need to be unique? If so I would check whether it already exists before starting the inserts.
Similarly, I would pass in the @CopyFromUser directly, rather than looking it up from the first and last name. If you have two people with the same name they might not have the same permissions, so although the "select top 1" means you will get a single user, it might not be the right one.
If all users have the same permissions, then it doesn't matter who you copy, but you could hard code the permissions or set defaults on the columns.
It appears that the same code is repeated for multiple linked servers and databases. If this is the case then you could define the code once in a stored procedure and execute it multiple times. If the code is not the same then obviously this doesn't apply.
August 15, 2022 at 3:48 pm
Thanks for the heads-up, done and Codeblock function noted for future reference!
August 15, 2022 at 4:10 pm
The table structures are identical across the estate, so I had wondered if they could - or should - be consolidated somehow.
We copied from about a dozen users who all had different permissions to different business subsections. They also had distinct names, so I hadn't made any effort to account for a second Chris Smith or Mohammed Khan joining, as it was I barely beat the deadline to deliver the script. My problem had been that some of the dirty dozen ( 🙂 ) had multiple accounts, but IIRC those duplicates at least held the same permissions as the original, so until someone was tasked with the due diligence to dedupe, I stuck with my remit to provide a script that took as much of the ball-ache from my colleagues as possible. By way of background, for years they had been using a very, very user-hostile GUI that took between an hour, to an entire day to add said new user, depending on how many subsections of the business the new guy would be involved with. When I remarked on the sheer insanity of one of our department spending that time, given we were adding 1-2 new users weekly, my boss kindly gave me (as a contractor) the time/latitude to pursue a solution. The script's runtime was 13 seconds tops, so... 🙂
That project that lead to this script is probably worth a post here of it's own, I may write it one day.
August 15, 2022 at 6:16 pm
The table structures are identical across the estate, so I had wondered if they could - or should - be consolidated somehow.
Possibly a Partitioned View. If it's across servers, you should probably just do a loop through several linked servers.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2022 at 6:30 pm
I would find having "-- (Values)" between the insert and select statements very annoying.
For me having more new lines and some decent indentation would make it a lot more maintainable.
----------------------------------------------------------------------------------------------------------------------------------
--Insert Operator Script (IOS)
--The purpose of IOS is to add new users to Tampex (known within the software as "Operators"). You will need:
-- 1) To connect this script to 10.**.**.** (Live);
-- 2) The full name of the new user, along with the name of an existing accountholder whose permissions the script will replicate,
-- and to add this information to the 4 variables immediately below.
DECLARE @CopyFromUserFull VARCHAR(20) = 'Susan Boyle'; --The name of the existing user
DECLARE @UserForename VARCHAR(20) = 'Cindy'; --The new user's first name here
DECLARE @UserSurname VARCHAR(20) = 'Crawford'; --The new users surname here
DECLARE @UserOperator VARCHAR(10) = 'CINDYC'; --AKA Operator Reference - this is usually firstname + first letter of surname, --ie Chris Smith = CHRISS and may NOT be more than 10 characters!!
--Then just press F5. The script will return a list, in the following format, of instances where it found the existing accountholder and created your new user,
-- all you now need to do is add the password:
-- Instance Operator_Ref
-------- ------------
-- Test CABARET CCRAWFORD
----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------
--Insert Operator Script (IOS)
--The purpose of IOS is to add new users to Tampex (known within the software as "Operators"). You will need:
-- 1) To connect this script to 10.**.**.** (Live);
-- 2) The full name of the new user, along with the name of an existing accountholder whose permissions the script will replicate,
-- and to add this information to the 4 variables immediately below.
DECLARE @CopyFromUserFull VARCHAR(20) = 'Susan Boyle'; --The name of the existing user
DECLARE @UserForename VARCHAR(20) = 'Cindy'; --The new user's first name here
DECLARE @UserSurname VARCHAR(20) = 'Crawford'; --The new users surname here
DECLARE @UserOperator VARCHAR(10) = 'CINDYC'; --AKA Operator Reference - this is usually firstname + first letter of surname, --ie Chris Smith = CHRISS and may NOT be more than 10 characters!!
--Then just press F5. The script will return a list, in the following format, of instances where it found the existing accountholder and created your new user,
-- all you now need to do is add the password:
-- Instance Operator_Ref
-------- ------------
-- Test CABARET CCRAWFORD
----------------------------------------------------------------------------------------------------------------------------------
DECLARE @CopyFromUser VARCHAR(10) =
(
SELECT TOP (1) *
FROM (SELECT Operator_Ref
FROM [ProdServerName].[TampexLive_CABARET].[dbo].Operator
WHERE Operator_Forename + ' ' + Operator_Surname = @CopyFromUserFull
UNION
SELECT Operator_Ref
FROM [ProdServerName].[TampexLive_SARGON].[dbo].Operator
WHERE Operator_Forename + ' ' + Operator_Surname = @CopyFromUserFull
UNION
SELECT Operator_Ref
FROM [ProdServerName].[TampexLive_PROF].[dbo].Operator
WHERE Operator_Forename + ' ' + Operator_Surname = @CopyFromUserFull
UNION
SELECT Operator_Ref
FROM [ProdServerName].[TampexLive_C777].[dbo].Operator
WHERE Operator_Forename + ' ' + Operator_Surname = @CopyFromUserFull
UNION
SELECT Operator_Ref
FROM [ProdServerName].[TampexLive_ROI].[dbo].Operator
WHERE Operator_Forename + ' ' + Operator_Surname = @CopyFromUserFull
) AS Tia
);
--Begin insertions to Live Environment
INSERT INTO [ProdServerName].[TampexLive_CABARET].[dbo].Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerName].[TampexLive_CABARET].[dbo].Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_CABARET].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerName].[TampexLive_CABARET].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_CABARET].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerName].[TampexLive_CABARET].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'BA Live' AS Instance,
Operator_Ref
FROM [ProdServerName].[TampexLive_CABARET].dbo.Operator
WHERE Operator_Ref = @UserOperator;
--Use ProdServerName.TampexLive_C777
-- INSERT STATEMENTS
INSERT INTO [ProdServerName].[TampexLive_C777].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerName].[TampexLive_C777].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_C777].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerName].[TampexLive_C777].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_C777].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerName].[TampexLive_C777].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'C777 Live' AS Instance,
Operator_Ref
FROM [ProdServerName].[TampexLive_C777].dbo.Operator
WHERE Operator_Ref = @UserOperator;
--Use [ProdServerName].[TampexLive_PROF]
-- INSERT STATEMENTS
INSERT INTO [ProdServerName].[TampexLive_PROF].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerName].[TampexLive_PROF].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_PROF].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerName].[TampexLive_PROF].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_PROF].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerName].[TampexLive_PROF].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'PROF Live' AS Instance,
Operator_Ref
FROM [ProdServerName].[TampexLive_PROF].dbo.Operator
WHERE Operator_Ref = @UserOperator;
-- INSERT STATEMENTS
INSERT INTO [ProdServerName].[TampexLive_SARGON].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerName].[TampexLive_SARGON].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_SARGON].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerName].[TampexLive_SARGON].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_SARGON].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerName].[TampexLive_SARGON].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'SARGON Live' AS Instance,
Operator_Ref
FROM [ProdServerName].[TampexLive_SARGON].dbo.Operator
WHERE Operator_Ref = @UserOperator;
--Use [ProdServerName].[TampexLive_ROI].
INSERT INTO [ProdServerName].[TampexLive_ROI].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerName].[TampexLive_ROI].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_ROI].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerName].[TampexLive_ROI].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerName].[TampexLive_ROI].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerName].[TampexLive_ROI].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'ROI Live' AS Instance,
Operator_Ref
FROM [ProdServerName].[TampexLive_ROI].dbo.Operator_Groups
WHERE Operator_Ref = @UserOperator;
--Begin insertions to Test Environment
INSERT INTO [ProdServerNameTest].[TampexTest_CABARET].[dbo].Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerNameTest].[TampexTest_CABARET].[dbo].Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_CABARET].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerNameTest].[TampexTest_CABARET].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_CABARET].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerNameTest].[TampexTest_CABARET].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'Test CABARET' AS Instance,
Operator_Ref
FROM [ProdServerNameTest].[TampexTest_CABARET].dbo.Operator
WHERE Operator_Ref = @UserOperator;
--Use ProdServerNameTest.TampexTest_C777
INSERT INTO [ProdServerNameTest].[TampexTest_C777].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerNameTest].[TampexTest_C777].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_C777].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerNameTest].[TampexTest_C777].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_C777].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerNameTest].[TampexTest_C777].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'Test C777' AS Instance,
Operator_Ref
FROM [ProdServerNameTest].[TampexTest_C777].dbo.Operator
WHERE Operator_Ref = @UserOperator;
--Use [ProdServerNameTest].[TampexTest_PROF]
INSERT INTO [ProdServerNameTest].[TampexTest_PROF].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerNameTest].[TampexTest_PROF].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_PROF].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerNameTest].[TampexTest_PROF].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_PROF].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerNameTest].[TampexTest_PROF].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'Test_PROF' AS Instance,
Operator_Ref
FROM [ProdServerNameTest].[TampexTest_PROF].dbo.Operator
WHERE Operator_Ref = @UserOperator;
--Use [ProdServerNameTest].[TampexTest_SARGON].
INSERT INTO [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_SARGON].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerNameTest].[TampexTest_SARGON].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'Test_SARGON' AS Instance,
Operator_Ref
FROM [ProdServerNameTest].[TampexTest_SARGON].dbo.Operator
WHERE Operator_Ref = @UserOperator;
--Use [ProdServerNameTest].[TampexTest_ROI].
INSERT INTO [ProdServerNameTest].[TampexTest_ROI].dbo.Operator
(
Operator_Ref,
Operator_Forename,
Operator_Surname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
Must_Enter_Password,
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
)
SELECT @UserOperator,
@UserForename,
@UserSurname,
Multiple_Logging,
Usage_Logging,
Screen_Access_Mode,
Flip_Fld_In_Scrn_Mod,
Flip_Fld_In_Line_Mod,
Display_Buttons,
Confirm_Box_Default,
Confirm_Print_Dest,
Allow_Alarm,
Allow_Mail,
'T',
Load_Tree_Menu_On_Login_TF,
Dataview_Code,
Expired_TF,
Report_CSV_Folder,
Open_CSV_Reports,
CSV_Report_Viewer_Path,
Active_Directory_User_TF,
Active_Directory_Login_Name,
Allow_Windows_Login_TF,
Portal_Username,
TampexAutomationEmailAddress
FROM [ProdServerNameTest].[TampexTest_ROI].dbo.Operator
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_ROI].dbo.Valid_Employers
(
Operator_Ref,
Employer_Ref
)
SELECT @UserOperator,
Employer_Ref
FROM [ProdServerNameTest].[TampexTest_ROI].dbo.Valid_Employers
WHERE Operator_Ref = @CopyFromUser;
INSERT INTO [ProdServerNameTest].[TampexTest_ROI].dbo.Operator_Groups
(
Group_Ref,
Operator_Ref
)
SELECT Group_Ref,
@UserOperator
FROM [ProdServerNameTest].[TampexTest_ROI].dbo.Operator_Groups
WHERE Operator_Ref = @CopyFromUser;
SELECT 'Test_ROI' AS Instance,
Operator_Ref
FROM [ProdServerNameTest].[TampexTest_ROI].dbo.Operator_Groups
WHERE Operator_Ref = @UserOperator;
August 17, 2022 at 1:07 pm
I would find having "-- (Values)" between the insert and select statements very annoying.
How would you have changed that, preferably? IIRC the final select statement after each Insert statement was needed (for business reasons) to list instances where an insertion occurred, and unless I'm mistaken the Insert syntax dictates the values need to follow the columns into which the insert should occur.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply