DECLARE @CAMINHO VARCHAR (MAX) = ''

IF (SELECT CHARINDEX('\',REVERSE(@CAMINHO),1)) = 1
BEGIN

SET @CAMINHO = (SELECT REVERSE(SUBSTRING(REVERSE(@CAMINHO),2,(LEN(REVERSE(@CAMINHO))))))

END;


EXEC('
EXEC sys.sp_configure ''show advanced options'', 1;

RECONFIGURE;

EXEC sys.sp_configure ''xp_cmdshell'', 1;

RECONFIGURE;
');

	IF OBJECT_ID ('TEMPDB..#CADCEP_ERP') IS NOT NULL
	BEGIN
		DROP TABLE #CADCEP_ERP
	END;
	
	CREATE TABLE #CADCEP_ERP
	(
	[NOME_LOG] [varchar] (110) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LOCAL_LOG] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BAIRRO_1_LOG] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BAIRRO_2_LOG] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CEP5_LOG] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CEP8_LOG] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UF_LOG] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TIPO_LOG] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OBS_NOME_LOG] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DATA_IMPORTACAO] [datetime] NULL,
	[VERSAO_LAYOUT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [XIE1CADCEP_ERP] ON #CADCEP_ERP ([CEP8_LOG]) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [XIE2CADCEP_ERP] ON #CADCEP_ERP ([LOCAL_LOG], [UF_LOG]) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [XIE3CADCEP_ERP] ON #CADCEP_ERP ([NOME_LOG]) ON [PRIMARY];


SET QUOTED_IDENTIFIER OFF

	IF OBJECT_ID ('TEMPDB..#v_path') IS NOT NULL
	BEGIN 
		DROP TABLE #v_path
	END;

	CREATE TABLE #v_path(caminho VARCHAR (max))

	IF @CAMINHO <> ''
	BEGIN
		
		INSERT INTO #v_path (caminho)
		VALUES ('bcp_queryout_ERP_112019.txt')

	
		declare @PathFileName varchar(100)
		SET @PathFileName = (SELECT TOP 1 @CAMINHO + '\' + caminho FROM #v_path);
		

		--Step 2: BULK INSERT Statement
		DECLARE @SQL varchar(2000)
		  SET @SQL = "BULK INSERT #CADCEP_ERP FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '@',CODEPAGE = 'AGP') "

		  --Step 3: Execute BULK INSERT statement

		EXEC (@SQL)
	END;

	ELSE

	INSERT INTO #v_path (caminho)
	EXEC xp_cmdshell 'dir /b c:\bcp_queryout_ERP_112019.txt'
	
	declare @PathFileName2 varchar(100)
	SET @PathFileName2 = (SELECT TOP 1 'C:\' + caminho FROM #v_path);

	--Step 2: BULK INSERT Statement
	DECLARE @SQL2 varchar(2000)
	  SET @SQL2 = "BULK INSERT #CADCEP_ERP FROM '"+@PathFileName2+"' WITH (FIELDTERMINATOR = '@',CODEPAGE = 'AGP') "

	  --Step 3: Execute BULK INSERT statement

	EXEC (@SQL2);


INSERT INTO dbo.CADCEP
(
    NOME_LOG,
    LOCAL_LOG,
    BAIRRO_1_LOG,
    BAIRRO_2_LOG,
    CEP5_LOG,
    CEP8_LOG,
    UF_LOG,
    TIPO_LOG,
    OBS_NOME_LOG,
    DATA_IMPORTACAO,
    VERSAO_LAYOUT
)
SELECT 
A.NOME_LOG,
A.LOCAL_LOG,
A.BAIRRO_1_LOG,
ISNULL(A.BAIRRO_2_LOG,'') AS BAIRRO_2_LOG,
A.CEP5_LOG,
A.CEP8_LOG,
A.UF_LOG,
A.TIPO_LOG,
A.OBS_NOME_LOG,
A.DATA_IMPORTACAO,
A.VERSAO_LAYOUT
FROM #CADCEP_ERP A
LEFT JOIN dbo.CADCEP B
ON A.CEP8_LOG = B.CEP8_LOG
WHERE A.CEP8_LOG IS NOT NULL
AND	B.CEP8_LOG IS NULL

PRINT 'NUMERO DE REGISTROS INSERIDOS ' + CAST(@@ROWCOUNT AS VARCHAR (123)) + ' (ROWS)'

