--PASO NUMERO 1 TIEMPO APROXIMADO EN TRAER DICHA INFORMACION 20 MIN.
/*Instrucciones BCP para extraer los registros de una base de datos a BCP SE REQUIERE VPN EN ESTE PUNTO SOLAMENTE, SE EJECUTAN EN UN CMD*/
/*Copia a BCP las llamadas del mes a trabajar*/
bcp "select * from asistel.dbo.tblcalldetails where calldetails_lpbxid=1 and calldetails_scalltype not in ('ent','red') and year(calldetails_tdate)=2009 and month(calldetails_tdate)=4" queryout C:\BCP\LlamadasAbril2009.bcp -n -S10.1.8.215,3731 -Uasistel -PAsistelProd8
/*Copia a BCP los registros del mes a trabajar*/
bcp "select * from asistel.dbo.tblcdrpbx where collectors_lcollectorid=1 and year(cdrpbx_ttime)=2009 and month(cdrpbx_ttime)=4" queryout C:\BCP\RegistroAbrilO2009.bcp -n -S10.1.8.215,3731 -Uasistel -PAsistelProd8
--PASO NUMERO 2
-- EN LA DB PruebaJAGC REALIZAR LOS SIGUINETES TRUNCATE PARA QUE QUEDEN LIBRES DE INFORMACION
TRUNCATE TABLE TblCdrPbx2
TRUNCATE TABLE TblCallDetails2
-- PASO NUMERO 3
/*Instrucciones BCP para introcudir en la base de datos desde un BCP, SE EJECUTAN EN UN CMD*/
bcp PruebaJAGC.dbo.tblcalldetails2 in c:\BCP\Llamadasabril2009.bcp -n -SALFASERVER2 -Usa -Palfacom64
bcp PruebaJAGC.dbo.tblcdrpbx2 in c:\BCP\Registrosabril2009.bcp -n -SALFASERVER2 -Usa -Palfacom64
-- PASO NUMERO 4
/*Copia los insertado por el bcp en una tabla TblCallDetails2 a la tabla TblCallDetails*/
BEGIN TRAN
EXEC('INSERT INTO dbo.TblCallDetails (CallDetails_lEmployeeId, CallDetails_sEmployeeDescription, CallDetails_sExtension, CallDetails_sExtensionDescription, CallDetails_sAutorizationCode, CallDetails_lCompanyId, CallDetails_sCompanyDescription, CallDetails_lOU1Id, CallDetails_sOU1Description, CallDetails_lOU2Id, CallDetails_sOU2Description, CallDetails_lOU3Id, CallDetails_sOU3Description, CallDetails_sDialNumber, CallDetails_sPlace, CallDetails_sState, CallDetails_sCountry, CallDetails_lDurationInMins, CallDetails_lDurationInSecs, CallDetails_sDuration, CallDetails_dCost, CallDetails_sTrunk, CallDetails_tDate, CallDetails_sCallType, CallDetails_lRemoteId, CallDetails_bRevEmployee, CallDetails_bRevSupervisor, CallDetails_lPbxId, CallDetails_lCallStatus, CallDetails_lOwnerId, CallDetails_sOwnerDescription, CallDetails_sComment, CallDetails_lSupervisorId, CallDetails_tAuditDate, CallDetails_bOwnerType, CallDetails_bValid)
SELECT CallDetails_lEmployeeId, CallDetails_sEmployeeDescription, CallDetails_sExtension, CallDetails_sExtensionDescription, CallDetails_sAutorizationCode, CallDetails_lCompanyId, CallDetails_sCompanyDescription, CallDetails_lOU1Id, CallDetails_sOU1Description, CallDetails_lOU2Id, CallDetails_sOU2Description, CallDetails_lOU3Id, CallDetails_sOU3Description, CallDetails_sDialNumber, CallDetails_sPlace, CallDetails_sState, CallDetails_sCountry, CallDetails_lDurationInMins, CallDetails_lDurationInSecs, CallDetails_sDuration, CallDetails_dCost, CallDetails_sTrunk, CallDetails_tDate, CallDetails_sCallType, CONVERT(bigint, CallDetails_lRemoteId), CallDetails_bRevEmployee, CallDetails_bRevSupervisor, CallDetails_lPbxId, CallDetails_lCallStatus, CallDetails_lOwnerId, CallDetails_sOwnerDescription, CallDetails_sComment, CallDetails_lSupervisorId, CallDetails_tAuditDate, CallDetails_bOwnerType, CallDetails_bValid FROM dbo.TblCallDetails2')
commit
-- TIEMPO APROXIMADO CON 66,344 LLAMADAS 55 SEGS.
/*Copia los insertado por el bcp en una tabla TblCdrPbx2 a la tabla TblCdrPbx*/
BEGIN TRAN
EXEC('INSERT INTO dbo.TblCdrPbx ( CdrPbx_lRemoteId, CdrPbx_sRegister, Collectors_lCollectorID, CdrPbx_tTime, CdrPbx_bIsProcessed, CdrPbx_bReaded,CdrPbx_tDateReaded)
SELECT CONVERT(bigint, CdrPbx_lRemoteId), CdrPbx_sRegister, Collectors_lCollectorID, CdrPbx_tTime, CdrPbx_bIsProcessed, CdrPbx_bReaded, CASE WHEN CdrPbx_bReaded=1 THEN CdrPbx_tTime ELSE convert(datetime,''01/01/1900 00:00:00'',103 ) END FROM dbo.TblCdrPbx2')
commit
-- TIEMPO APROXIMADO CON 210,368 REGISTROS 26 SEGS.