How could I have written this script better?

  • 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 1 month, 3 weeks ago by  JaybeeSQL. Reason: Added readability
    • This topic was modified 1 month, 3 weeks ago by  JaybeeSQL.
    • This topic was modified 1 month, 3 weeks ago by  JaybeeSQL.
  • Is there any chance that you can post a properly formatted version inside a code block. It is unreadable as it stands.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

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

  • 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 1 month, 3 weeks ago by  JaybeeSQL.
    • This reply was modified 1 month, 3 weeks ago by  JaybeeSQL.
    • This reply was modified 1 month, 3 weeks ago by  JaybeeSQL.
  • 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)
    Intro to Tally Tables and Functions

  • 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;
  • 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.

     

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

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