How could I have written this script better?

Question

How could I have written this script better?

Hall of Fame

Points: 3501

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

This topic was modified 2 years, 3 months ago by JaybeeSQL. Reason: Added readability
This topic was modified 2 years, 3 months ago by JaybeeSQL.
This topic was modified 2 years, 3 months ago by JaybeeSQL.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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

Ed B Hall of Fame Points: 3666 More actions · Answer 2

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.

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 3

Thanks for the heads-up, done and Codeblock function noted for future reference!

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 4

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.

This reply was modified 2 years, 3 months ago by JaybeeSQL.
This reply was modified 2 years, 3 months ago by JaybeeSQL.
This reply was modified 2 years, 3 months ago by JaybeeSQL.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

JaybeeSQL wrote:

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

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)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

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;

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 7

Jonathan AC Roberts wrote:

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.