November 17, 2014 at 4:39 am
Hi Friends I am Using the Below mentioned Script to fetch the data from Flatfile table and i need to populate it into respective Parent and Child table by Cursur.
In between the Scripts i am calling the Error Log Table by using the Path from the local folder where the Error Log table script is available.
Now i need to run this through Stored Procedure.
Please guide me how to create Stored Procedure and how to load datas into Flatfile table and from Flatfile Table to Parent and Child Table.
--:r "C:\Clients\Blackbook\Blackbookmarketing\SQLScripts\SETENVIRONMENT.sql"
-- ============---=========================================================SERVICE_APPOINTMENT_CURSUR===================================================================
USE [IconicMarketing]
GO
DECLARE
@FileType varchar (50),
@ACDealerID varchar (50),
@ClientDealerID varchar (50),
@DMSType varchar (50),
@AppointmentNumber Varchar(20),
@RONumber varchar (258),
@CustomerName varchar (50),
@CustomerHomePhone varchar(100),
@CustomerEmailAddress varchar (50),
@AppointmentDate date,
@AppointmentTime date,
@VehicleYear varchar(100),
@VehicleMake varchar (50),
@VehicleModel varchar (50),
@VehicleVIN varchar (50),
@ServiceAdvisorNumber varchar(200),
@OperationCode varchar (50),
@ComplaintStatement varchar (302),
@Comments varchar (max),
@CustomerFirstName varchar (50),
@CustomerLastName varchar (50),
@CustomerAddress varchar (50),
@CustomerCity varchar (50),
@CustomerState varchar (50),
@CustomerZip varchar (12),
@CustomerCellPhone varchar (15),
@CustomerNumber varchar (15),
@CustomerWorkPhone varchar (15),
@Department varchar (50),
@CASS_STD_LINE1 varchar (50),
@CASS_STD_LINE2 varchar (50),
@CASS_STD_CITY varchar (50),
@CASS_STD_STATE varchar (50),
@CASS_STD_ZIP bigint,
@CASS_STD_ZIP4 varchar (255),
@CASS_STD_DPBC varchar (255),
@CASS_STD_CHKDGT varchar (255),
@CASS_STD_CART varchar (50),
@CASS_STD_LOT varchar (255),
@CASS_STD_LOTORD varchar (50),
@CASS_STD_URB varchar (255),
@CASS_STD_FIPS varchar (255),
@CASS_STD_EWS varchar (50),
@CASS_STD_LACS varchar (255),
@CASS_STD_ZIPMOV bigint,
@CASS_STD_Z4LOM varchar (50),
@CASS_STD_NDIAPT varchar (255),
@CASS_STD_NDIRR varchar (255),
@CASS_STD_LACSRT varchar(255),
@CASS_STD_ERROR_CD varchar (10),
@NCOA_AC_ID bigint,
@myerror Varchar(500),
@ServiceAppointID int,
@errornumber int,
@errorseverity varchar(500),
@errorstate int,
@errorprocedure varchar(500),
@errorline varchar(50),
@errormessage varchar(1000),
@errortable varchar(50),
@SAI_Id INT,
@createddate datetime,
@flatfile_createddate datetime,
@FtpDate date;
DECLARE SERVICE_APPOINTMENT_Cursor CURSOR FOR
SELECT * from FLATFILE_SERVICE_APPOINTMENT where CONVERT (date,flatfile_createddate) = CONVERT (date,GETDATE()) order by flatfile_createddate
OPEN SERVICE_APPOINTMENT_Cursor
FETCH NEXT FROM SERVICE_APPOINTMENT_Cursor
INTO
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@createddate,
@flatfile_createddate,
@FtpDate;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @VehicleVIN ;
IF Exists (Select 1 From DMS_SERVICE_APPOINTMENT Where DMSDealerID = @ClientDealerID And AppointmentNumber = @AppointmentNumber
And CustomerNumber = @CustomerNumber AND AppointmentDate = @AppointmentDate )
Begin
Select @SAI_Id = ID From DMS_SERVICE_APPOINTMENT Where DMSDealerID = @ClientDealerID And AppointmentNumber = @AppointmentNumber
And CustomerNumber = @CustomerNumber AND AppointmentDate = @AppointmentDate
Delete From [DMS_SERVICE_APPOINTMENT_CUSTOMER]
Where DMSServiceAppointID = @SAI_Id
Delete From [dbo].[DMS_SERVICE_APPOINTMENT_DETAILS]
Where DMSServiceAppointID = @SAI_Id
Delete From [dbo].[DMS_SERVICE_APPOINTMENT_VEHICLE]
Where DMSServiceAppointID = @SAI_Id
Delete From DMS_SERVICE_APPOINTMENT
Where ID = @SAI_Id
End
--================================================================================================================================================
-- ***************************************************** INSERT INTO SERVICE_APPOINTMENT********************************************************
--================================================================================================================================================
BEGIN TRY
INSERT INTO DMS_SERVICE_APPOINTMENT
(
DMSDealerID,
AppointmentNumber,
RONumber,
Customernumber,
DMSType,
AppointmentDate,
FtpDate
)
VALUES (
@ClientDealerID,
@AppointmentNumber,
@RONumber,
@Customernumber,
@DMSType,
@AppointmentDate,
@FtpDate
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE(),
@errortable = 'DMS_SERVICE_APPOINTMENT',
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();
--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[DMSLOG_SERVICE_APPOINTMENT_ERROR]
(
FileType,
ACDealerID,
ClientDealerID,
DMSType,
AppointmentNumber ,
RONumber,
CustomerName,
CustomerHomePhone,
CustomerEmailAddress,
AppointmentDate,
AppointmentTime,
VehicleYear,
VehicleMake,
VehicleModel,
VehicleVIN,
ServiceAdvisorNumber,
OperationCode,
ComplaintStatement,
Comments,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity ,
CustomerState,
CustomerZip,
CustomerCellPhone,
CustomerNumber,
CustomerWorkPhone,
Department,
CASS_STD_LINE1,
CASS_STD_LINE2,
CASS_STD_CITY,
CASS_STD_STATE,
CASS_STD_ZIP,
CASS_STD_ZIP4,
CASS_STD_DPBC,
CASS_STD_CHKDGT,
CASS_STD_CART,
CASS_STD_LOT,
CASS_STD_LOTORD,
CASS_STD_URB,
CASS_STD_FIPS,
CASS_STD_EWS,
CASS_STD_LACS,
CASS_STD_ZIPMOV,
CASS_STD_Z4LOM,
CASS_STD_NDIAPT,
CASS_STD_NDIRR,
CASS_STD_LACSRT,
CASS_STD_ERROR_CD,
NCOA_AC_ID,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE,
FtpDate)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable,
@FtpDate)
;
END CATCH
--PRINT @errornumber;
--PRINT @errorseverity;
--PRINT @errorprocedure;
--PRINT @errorline;
--PRINT @errormessage;
--PRINT @errorstate;
set @myerror = @@ERROR;
-- This PRINT statement prints 'Error = 0' because
-- @@ERROR is reset in the IF statement above.
PRINT N'Error = ' + @myerror;
set @ServiceAppointID = scope_identity();
PRINT @ServiceAppointID;
--================================================================================================================================================
-- *********************************************Insert into DMS_SERVICE_APPOINTMENT_CUSTOMER Table****************************************************************
--================================================================================================================================================
BEGIN TRY
INSERT INTO DMS_SERVICE_APPOINTMENT_CUSTOMER
(
DMSServiceAppointID,
CustomerName,
CustomerHomePhone,
CustomerEmailAddress,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity,
CustomerState,
CustomerZip,
CustomerCellPhone,
CustomerNumber,
CustomerWorkPhone,
CASS_STD_LINE1,
CASS_STD_LINE2,
CASS_STD_CITY,
CASS_STD_STATE,
CASS_STD_ZIP,
CASS_STD_ZIP4,
CASS_STD_DPBC,
CASS_STD_CHKDGT,
CASS_STD_CART,
CASS_STD_LOT,
CASS_STD_LOTORD,
CASS_STD_URB,
CASS_STD_FIPS,
CASS_STD_EWS,
CASS_STD_LACS,
CASS_STD_ZIPMOV,
CASS_STD_Z4LOM,
CASS_STD_NDIAPT,
CASS_STD_NDIRR,
CASS_STD_LACSRT,
CASS_STD_ERROR_CD,
NCOA_AC_ID,
FtpDate
)
VALUES
(
@ServiceAppointID,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@FtpDate
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE() ,
@errortable = 'DMS_SERVICE_APPOINTMENT_CUSTOMER',
@errorprocedure = ERROR_PROCEDURE() ,
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();
--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[DMSLOG_SERVICE_APPOINTMENT_ERROR]
(
FileType,
ACDealerID,
ClientDealerID,
DMSType,
AppointmentNumber ,
RONumber,
CustomerName,
CustomerHomePhone,
CustomerEmailAddress,
AppointmentDate,
AppointmentTime,
VehicleYear,
VehicleMake,
VehicleModel,
VehicleVIN,
ServiceAdvisorNumber,
OperationCode,
ComplaintStatement,
Comments,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity ,
CustomerState,
CustomerZip,
CustomerCellPhone,
CustomerNumber,
CustomerWorkPhone,
Department,
CASS_STD_LINE1,
CASS_STD_LINE2,
CASS_STD_CITY,
CASS_STD_STATE,
CASS_STD_ZIP,
CASS_STD_ZIP4,
CASS_STD_DPBC,
CASS_STD_CHKDGT,
CASS_STD_CART,
CASS_STD_LOT,
CASS_STD_LOTORD,
CASS_STD_URB,
CASS_STD_FIPS,
CASS_STD_EWS,
CASS_STD_LACS,
CASS_STD_ZIPMOV,
CASS_STD_Z4LOM,
CASS_STD_NDIAPT,
CASS_STD_NDIRR,
CASS_STD_LACSRT,
CASS_STD_ERROR_CD,
NCOA_AC_ID,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE,
FtpDate)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable,
@FtpDate)
;
END CATCH
--================================================================================================================================================
-- **************************************************Insert into DMS_SERVICE_APPOINTMENT_DETAILS Table************************************************************
--================================================================================================================================================
BEGIN TRY
INSERT INTO DMS_SERVICE_APPOINTMENT_DETAILS
(
DMSServiceAppointID,
AppointmentTime,
ServiceAdvisorNumber,
OperationCode,
ComplaintStatement,
Comments,
Department,
FtpDate
)
VALUES (
@ServiceAppointID,
@AppointmentTime,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@Department,
@FtpDate
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE() ,
@errortable = 'DMS_SERVICE_APPOINTMENT_DETAILS',
@errorprocedure = ERROR_PROCEDURE() ,
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();
--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[DMSLOG_SERVICE_APPOINTMENT_ERROR]
(
FileType,
ACDealerID,
ClientDealerID,
DMSType,
AppointmentNumber ,
RONumber,
CustomerName,
CustomerHomePhone,
CustomerEmailAddress,
AppointmentDate,
AppointmentTime,
VehicleYear,
VehicleMake,
VehicleModel,
VehicleVIN,
ServiceAdvisorNumber,
OperationCode,
ComplaintStatement,
Comments,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity ,
CustomerState,
CustomerZip,
CustomerCellPhone,
CustomerNumber,
CustomerWorkPhone,
Department,
CASS_STD_LINE1,
CASS_STD_LINE2,
CASS_STD_CITY,
CASS_STD_STATE,
CASS_STD_ZIP,
CASS_STD_ZIP4,
CASS_STD_DPBC,
CASS_STD_CHKDGT,
CASS_STD_CART,
CASS_STD_LOT,
CASS_STD_LOTORD,
CASS_STD_URB,
CASS_STD_FIPS,
CASS_STD_EWS,
CASS_STD_LACS,
CASS_STD_ZIPMOV,
CASS_STD_Z4LOM,
CASS_STD_NDIAPT,
CASS_STD_NDIRR,
CASS_STD_LACSRT,
CASS_STD_ERROR_CD,
NCOA_AC_ID,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE,
FtpDate)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable,
@FtpDate)
;
END CATCH
--================================================================================================================================================
-- **************************************************Insert into DMS_SERVICE_APPOINTMENT_VEHICLE Table************************************************
--================================================================================================================================================
BEGIN TRY
INSERT INTO DMS_SERVICE_APPOINTMENT_VEHICLE
(
DMSServiceAppointID,
VehicleYear,
VehicleMake,
VehicleModel,
VehicleVIN,
FtpDate
)
VALUES
(
@ServiceAppointID,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@FtpDate
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER(),
@errorseverity = ERROR_SEVERITY() ,
@errorstate = ERROR_STATE() ,
@errortable = 'DMS_SERVICE_APPOINTMENT_VEHICLE',
@errorprocedure = ERROR_PROCEDURE() ,
@errorline = ERROR_LINE(),
@errormessage = ERROR_MESSAGE();
--:r $(serviceappt_errorinsert)
INSERT INTO [dbo].[DMSLOG_SERVICE_APPOINTMENT_ERROR]
(
FileType,
ACDealerID,
ClientDealerID,
DMSType,
AppointmentNumber ,
RONumber,
CustomerName,
CustomerHomePhone,
CustomerEmailAddress,
AppointmentDate,
AppointmentTime,
VehicleYear,
VehicleMake,
VehicleModel,
VehicleVIN,
ServiceAdvisorNumber,
OperationCode,
ComplaintStatement,
Comments,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity ,
CustomerState,
CustomerZip,
CustomerCellPhone,
CustomerNumber,
CustomerWorkPhone,
Department,
CASS_STD_LINE1,
CASS_STD_LINE2,
CASS_STD_CITY,
CASS_STD_STATE,
CASS_STD_ZIP,
CASS_STD_ZIP4,
CASS_STD_DPBC,
CASS_STD_CHKDGT,
CASS_STD_CART,
CASS_STD_LOT,
CASS_STD_LOTORD,
CASS_STD_URB,
CASS_STD_FIPS,
CASS_STD_EWS,
CASS_STD_LACS,
CASS_STD_ZIPMOV,
CASS_STD_Z4LOM,
CASS_STD_NDIAPT,
CASS_STD_NDIRR,
CASS_STD_LACSRT,
CASS_STD_ERROR_CD,
NCOA_AC_ID,
ServiceAppointID ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORLINE ,
ERRORMESSAGE,
ERRORTABLE,
FtpDate)VALUES(
@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@ServiceAppointID ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORLINE ,
@ERRORMESSAGE,
@errortable,
@FtpDate)
;
END CATCH
---================================================================================================================================================
-- ***************************************************Move cursor to Next record ***************************************************************
--================================================================================================================================================
FETCH NEXT FROM SERVICE_APPOINTMENT_Cursor
INTO@FileType,
@ACDealerID,
@ClientDealerID,
@DMSType,
@AppointmentNumber,
@RONumber,
@CustomerName,
@CustomerHomePhone,
@CustomerEmailAddress,
@AppointmentDate,
@AppointmentTime,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleVIN,
@ServiceAdvisorNumber,
@OperationCode,
@ComplaintStatement,
@Comments,
@CustomerFirstName,
@CustomerLastName,
@CustomerAddress,
@CustomerCity,
@CustomerState,
@CustomerZip,
@CustomerCellPhone,
@CustomerNumber,
@CustomerWorkPhone,
@Department,
@CASS_STD_LINE1,
@CASS_STD_LINE2,
@CASS_STD_CITY,
@CASS_STD_STATE,
@CASS_STD_ZIP,
@CASS_STD_ZIP4,
@CASS_STD_DPBC,
@CASS_STD_CHKDGT,
@CASS_STD_CART,
@CASS_STD_LOT,
@CASS_STD_LOTORD,
@CASS_STD_URB,
@CASS_STD_FIPS,
@CASS_STD_EWS,
@CASS_STD_LACS,
@CASS_STD_ZIPMOV,
@CASS_STD_Z4LOM,
@CASS_STD_NDIAPT,
@CASS_STD_NDIRR,
@CASS_STD_LACSRT,
@CASS_STD_ERROR_CD,
@NCOA_AC_ID,
@createddate,
@flatfile_createddate,
@FtpDate;
END
CLOSE SERVICE_APPOINTMENT_Cursor;
DEALLOCATE SERVICE_APPOINTMENT_Cursor;
GO
SET ANSI_PADDING OFF
GO
-- ===============================================================================================================================================
-- *************************************************** END OF FLATFILE TABLE ***************************************************************
--================================================================================================================================================
November 17, 2014 at 7:32 am
arunnrj87 (11/17/2014)
Hi Friends I am Using the Below mentioned Script to fetch the data from Flatfile table and i need to populate it into respective Parent and Child table by Cursur.In between the Scripts i am calling the Error Log Table by using the Path from the local folder where the Error Log table script is available.
Now i need to run this through Stored Procedure.
Please guide me how to create Stored Procedure and how to load datas into Flatfile table and from Flatfile Table to Parent and Child Table.
I can help guide you but you may not like what I have to say. You need to rebuild this whole concept and get rid of that cursor. There is simply no need to use a cursor here at all. You have a few delete statements followed by some inserts. You can use OUTPUT and MERGE to help.
I can help you with the code but in order to offer any real help we need a few things:
1. Sample DDL in the form of CREATE TABLE statements.
2. Sample data in the form of INSERT INTO statements.
3. Expected results based on the sample data.
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply