BEGIN TRY
BEGIN TRANSACTION
-- Do your stuff that might fail here
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
DECLARE @ErrorState INT = ERROR_STATE()
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
https://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error
USE [CrouseScripts]
GO
/****** Object: StoredProcedure [dbo].[sp_UpdateLawsonMirror] Script Date: 4/10/2020 9:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_UpdateLawsonMirror]
AS
BEGIN TRY
BEGIN TRANSACTION
IF OBJECT_ID(N'crousescripts.dbo.tmpDepts', N'U') IS NOT NULL
drop table crousescripts.dbo.tmpDepts
exec crousescripts.dbo.createtable @path='n:\Deptcode.csv',@outTableName='crousescripts.dbo.tmpDepts'
delete from LawsonMirror.LawsonMirror.depts
INSERT INTO LawsonMirror.LawsonMirror.depts
(
[COMPANY]
,[PROCESS_LEVEL]
,[DEPARTMENT]
,[R_NAME]
,[DEP_DIST_CO]
,[DEP_ACCT_UNIT]
,[DEP_ACCOUNT]
,[DEP_SUB_ACCT]
,[SEC_LVL]
,[SEC_LOCATION]
,[SEGMENT_FLAG]
,[ACTIVE_FLAG]
,[USER_ID]
,[DATE_STAMP]
,[TIME_STAMP]
,[Col15]
)
select [COMPANY]
,[PROCESS_LEVEL]
,[DEPARTMENT]
,[R_NAME]
,[DEP_DIST_CO]
,[DEP_ACCT_UNIT]
,[DEP_ACCOUNT]
,[DEP_SUB_ACCT]
,[SEC_LVL]
,[SEC_LOCATION]
,[SEGMENT_FLAG]
,[ACTIVE_FLAG]
,[USER_ID]
,[DATE_STAMP]
,[TIME_STAMP]
,'' from crousescripts.dbo.tmpDepts
IF OBJECT_ID(N'crousescripts.dbo.tmpEmployee', N'U') IS NOT NULL
drop table crousescripts.dbo.tmpEmployee
exec crousescripts.dbo.createtable @path='n:\employee.csv',@outTableName='crousescripts.dbo.tmpEmployee'
delete from LawsonMirror.LawsonMirror.employee
INSERT INTO LawsonMirror.LawsonMirror.Employee
([COMPANY]
,[EMPLOYEE]
,[LAST_NAME]
,[FIRST_NAME]
,[MIDDLE_INIT]
,[MIDDLE_NAME]
,[NICK_NAME]
,[ADDR1]
,[ADDR2]
,[ADDR3]
,[ADDR4]
,[CITY]
,[STATE]
,[ZIP]
,[EMP_STATUS]
,[EIC_STATUS]
,[PROCESS_LEVEL]
,[DEPARTMENT]
,[USER_LEVEL]
,[JOB_CODE]
,[UNION_CODE]
,[SUPERVISOR]
,[DATE_HIRED]
,[R_SHIFT]
,[EXEMPT_EMP]
,[ADJ_HIRE_DATE]
,[ANNIVERS_DATE]
,[CREATION_DATE]
,[NBR_FTE]
,[PAY_GRADE]
,[EMAIL_ADDRESS]
,[POSITION]
,[WORK_SCHED]
,[SUPERVISOR_IND]
,[NAME_PREFIX]
,[NAME_SUFFIX]
,[LAST_NAME_PRE]
,[COUNTY])
select [COMPANY]
,[EMPLOYEE]
,[LAST_NAME]
,[FIRST_NAME]
,[MIDDLE_INIT]
,[MIDDLE_NAME]
,[NICK_NAME]
,[ADDR1]
,[ADDR2]
,[ADDR3]
,[ADDR4]
,[CITY]
,[STATE]
,[ZIP]
,[EMP_STATUS]
,[EIC_STATUS]
,[PROCESS_LEVEL]
,[DEPARTMENT]
,[USER_LEVEL]
,[JOB_CODE]
,[UNION_CODE]
,[SUPERVISOR]
,[DATE_HIRED]
,[R_SHIFT]
,[EXEMPT_EMP]
,[ADJ_HIRE_DATE]
,[ANNIVERS_DATE]
,[CREATION_DATE]
,[NBR_FTE]
,[PAY_GRADE]
,[EMAIL_ADDRESS]
,[POSITION]
,[WORK_SCHED]
,[SUPERVISOR_IND]
,[NAME_PREFIX]
,[NAME_SUFFIX]
,[LAST_NAME_PRE]
,[COUNTY]
from crousescripts.dbo.tmpEmployee
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [EMAIL_ADDRESS]='' WHERE [EMAIL_ADDRESS] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET MIDDLE_INIT='' WHERE MIDDLE_INIT is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [UNION_CODE]='' WHERE [UNION_CODE] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [USER_LEVEL]='' WHERE [USER_LEVEL] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [SUPERVISOR_IND]='' WHERE [SUPERVISOR_IND] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [PAY_GRADE]='' WHERE [PAY_GRADE]is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [SUPERVISOR]='' WHERE [SUPERVISOR]is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [NICK_NAME]='' WHERE [NICK_NAME] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [POSITION]='' WHERE [POSITION] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [MIDDLE_NAME]='' WHERE [MIDDLE_NAME] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR1]='' WHERE [ADDR1] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR2]='' WHERE [ADDR2] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR3]='' WHERE [ADDR3] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR4]='' WHERE [ADDR4] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [NAME_PREFIX]='' WHERE [NAME_PREFIX] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [NAME_SUFFIX]='' WHERE [NAME_SUFFIX] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [LAST_NAME_PRE]='' WHERE [LAST_NAME_PRE] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [COUNTY]='' WHERE [COUNTY] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [CITY]='' WHERE [CITY] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ZIP]='' WHERE [ZIP] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [STATE]='' WHERE [STATE] is NULL
IF OBJECT_ID(N'crousescripts.dbo.tmphrsuper', N'U') IS NOT NULL
drop table crousescripts.dbo.tmphrsuper
exec crousescripts.dbo.createtable @path='n:\hrsuper.csv',@outTableName='crousescripts.dbo.tmphrsuper'
delete from [LawsonMirror].[LawsonMirror].[SupervisorLU]
INSERT INTO [LawsonMirror].[LawsonMirror].[SupervisorLU] (
[COMPANY]
,[CODE]
,[DESCRIPTION]
,[EMPLOYEE]
,[OBJ_ID]
,[EFFECT_DATE]
,[ACTIVE_FLAG]
,[SUPER_RPTS_TO]
,[USER1]
,[USER2]
,[USER3]
,[USER4]
,[USER5]
,[HSUSET4_SS_SW])
SELECT [COMPANY]
,[CODE]
,[DESCRIPTION]
,[EMPLOYEE]
,[OBJ_ID]
,[EFFECT_DATE]
,[ACTIVE_FLAG]
,[SUPER_RPTS_TO]
,[USER1]
,[USER2]
,[USER3]
,[USER4]
,[USER5]
,[HSUSET4_SS_SW]
FROM crousescripts.dbo.tmphrsuper
UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [ACTIVE_FLAG]='' WHERE [ACTIVE_FLAG] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [SUPER_RPTS_TO]='' WHERE [SUPER_RPTS_TO] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER1]='' WHERE [USER1] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER2]='' WHERE [USER2] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER3]='' WHERE [USER3] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER4]='' WHERE [USER4] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER5]='' WHERE [USER5] is NULL
IF OBJECT_ID(N'crousescripts.dbo.tmpglnames', N'U') IS NOT NULL
drop table crousescripts.dbo.tmpglnames
exec crousescripts.dbo.createtable @path='n:\glnames.csv',@outTableName='crousescripts.dbo.tmpglnames'
--select * from crousescripts.dbo.tmpglnames
delete from [LawsonMirror].[LawsonMirror].[GL_Names]
INSERT INTO [LawsonMirror].[LawsonMirror].[GL_Names] (
[COMPANY]
,[ACCT_UNIT]
,[VAR_LEVELS]
,[DESCRIPTION]
,[CHART_SECTION]
,[CURRENCY_CODE]
,[PERSON_RESP]
,[ACTIVE_STATUS]
,[ACCT_GENERATE]
,[POSTING_FLAG]
,[VAR_LEVEL_DISP]
,[LEVEL_DEPTH]
,[LEVEL_DETAIL_01]
,[LEVEL_DETAIL_02]
,[LEVEL_DETAIL_03]
,[LEVEL_DETAIL_04]
,[LEVEL_DETAIL_05]
,[OBJ_ID]
,[PARENT_OBJ_ID]
,[GLNSET3_SS_SW])
SELECT [COMPANY]
,[ACCT_UNIT]
,[VAR_LEVELS]
,[DESCRIPTION]
,[CHART_SECTION]
,[CURRENCY_CODE]
,[PERSON_RESP]
,[ACTIVE_STATUS]
,[ACCT_GENERATE]
,[POSTING_FLAG]
,[VAR_LEVEL_DISP]
,[LEVEL_DEPTH]
,[LEVEL_DETAIL_01]
,[LEVEL_DETAIL_02]
,[LEVEL_DETAIL_03]
,[LEVEL_DETAIL_04]
,[LEVEL_DETAIL_05]
,[OBJ_ID]
,[PARENT_OBJ_ID]
,[GLNSET3_SS_SW]
FROM crousescripts.dbo.tmpglnames
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [VAR_LEVELS]='' WHERE [VAR_LEVELS] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [CHART_SECTION]='' WHERE [CHART_SECTION] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [CURRENCY_CODE]='' WHERE [CURRENCY_CODE] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [PERSON_RESP]='' WHERE [PERSON_RESP] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [VAR_LEVEL_DISP]='' WHERE [VAR_LEVEL_DISP] is NULL
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [ACCT_GENERATE]='' WHERE [ACCT_GENERATE] is NULL
/*UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET ='' WHERE is NULL
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET ='' WHERE is NULL
UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET ='' WHERE is NULL*/
IF OBJECT_ID(N'crousescripts.dbo.tmpJobCodes', N'U') IS NOT NULL
drop table crousescripts.dbo.tmpJobCodes
exec crousescripts.dbo.createtable @path='n:\jobcodes.csv',@outTableName='crousescripts.dbo.tmpJobCodes'
--select * from crousescripts.dbo.tmpglnames
delete from [LawsonMirror].[Lawson].[JOBCODES]
INSERT INTO [LawsonMirror].[Lawson].[JOBCODES] (
[COMPANY]
,[JOB_CODE]
,[DESCRIPTION]
,[RATE_OVERRIDE]
,[BEG_SAL_RANGE]
,[MID_SAL_RANGE]
,[END_SAL_RANGE]
,[POINTS]
,[EEO_CAT]
,[LAST_CMT_SEQ]
,[ACTIVE_FLAG]
,[ANNUAL_HOURS]
,[JOB_CLASS]
,[SCHEDULE]
,[PAY_GRADE]
,[EEO_SUB_CODE]
,[EXEMPT]
,[WC_CLASS]
,[CA_WC_CLASS]
,[PAY_RATE]
,[EXP_DIST_CO]
,[EXP_ACCT_UNIT]
,[EXP_ACCOUNT]
,[EXP_SUB_ACCT]
,[PROCESS_LEVEL]
,[DEPARTMENT]
,[REPORTS_TO]
,[APPROVED_BY]
,[TIPPED]
,[OBJ_ID]
,[MARKET_SALARY]
,[CURRENCY_CODE]
,[CURR_ND]
,[BASE_PAY_RATE]
,[BASE_CURRENCY]
,[BASE_ND]
,[BASE_BEG_SAL]
,[BASE_MID_SAL]
,[BASE_END_SAL]
,[BASE_MARKET]
,[SALARY_CLASS]
,[WEB_TYPE]
,[GENDER_CLASS]
,[DIR_IND_JOB]
,[OCCUP_GROUP]
,[TENURE_ELIG]
,[HIGH_ED_CAT]
,[HIGH_ED_SUBCAT]
,[ACADEMIC_RANK]
,[L_INDEX]
,[L_ATJBC_SS_SW]
)
SELECT [COMPANY]
,[JOB_CODE]
,[DESCRIPTION]
,[RATE_OVERRIDE]
,[BEG_SAL_RANGE]
,[MID_SAL_RANGE]
,[END_SAL_RANGE]
,[POINTS]
,[EEO_CAT]
,[LAST_CMT_SEQ]
,[ACTIVE_FLAG]
,[ANNUAL_HOURS]
,[JOB_CLASS]
,[SCHEDULE]
,[PAY_GRADE]
,[EEO_SUB_CODE]
,[EXEMPT]
,[WC_CLASS]
,[CA_WC_CLASS]
,[PAY_RATE]
,[EXP_DIST_CO]
,[EXP_ACCT_UNIT]
,[EXP_ACCOUNT]
,[EXP_SUB_ACCT]
,[PROCESS_LEVEL]
,[DEPARTMENT]
,[REPORTS_TO]
,[APPROVED_BY]
,[TIPPED]
,[OBJ_ID]
,[MARKET_SALARY]
,[CURRENCY_CODE]
,[CURR_ND]
,[BASE_PAY_RATE]
,[BASE_CURRENCY]
,[BASE_ND]
,[BASE_BEG_SAL]
,[BASE_MID_SAL]
,[BASE_END_SAL]
,[BASE_MARKET]
,[SALARY_CLASS]
,[WEB_TYPE]
,[GENDER_CLASS]
,[DIR_IND_JOB]
,[OCCUP_GROUP]
,[TENURE_ELIG]
,[HIGH_ED_CAT]
,[HIGH_ED_SUBCAT]
,[ACADEMIC_RANK]
,[L_INDEX]
,[L_ATJBC_SS_SW]
FROM crousescripts.dbo.tmpJobCodes
COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
DECLARE @ErrorState INT = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
-- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH