MergeVehicleByDmSCustomerId_Make_Model_Year

declare @VendorId int =2435

declare @NeedMergeCU table(dms_customerId varchar(50),  CountOf int)
declare @VEH table(customerId int, vehicleId int, ManYear int, Make varchar(50),Model varchar(50), vin varchar(17))
declare @cuMerge table(customerId int,vehicleId int)
insert into @NeedMergeCU
select c.dms_customerId, count(*) AS CountOf
from Customer as c
where c.VendorId= @VendorId and ltrim(rtrim(ISNULL(c.dms_customerId, ''))) !=''
and  (select count(*) from vehicle as v where v.CustomerId = c.CustomerId)>0
GROUP BY c.dms_customerId
HAVING COUNT(*) > 1

declare @TMPdms_customerId  varchar(50);
declare @MainCustomerId int =0;
declare @MainVehiclId int = 0;

DECLARE CUCursor CURSOR FOR
select dms_customerId from @NeedMergeCU
OPEN CUCursor;
    FETCH NEXT FROM CUCursor
        INTO @TMPdms_customerId;
        WHILE @@FETCH_STATUS = 0
        BEGIN
        delete from  @VEH;


        insert into @VEH
        Select c.CustomerId,v.vehicleId, ltrim(rtrim(ISNULL(v.ManYear, 0))) as ManYear,
        ltrim(rtrim(ISNULL(v.Make, ''))) as Make,
         REPLACE(ltrim(rtrim(ISNULL(Model, ''))), ' ', '') as Model,
         ltrim(rtrim(ISNULL(v.VinNum, ''))) as vin from Customer as c
        join Vehicle as v on c.CustomerId = v.CustomerId
         where c.DMS_CustomerId =  @TMPdms_customerId and c.VendorId= @VendorId

         delete @VEH where Len(Make)=0 or Len(Model)=0  or ManYear=0
         --DECLARE @TMP_customerId int=0
         --DECLARE  @TMP_vehicleId int=0
         DECLARE  @TMP_ManYear int =0
         DECLARE  @TMP_Make varchar(50)
         DECLARE @TMP_Model varchar(50)
         DECLARE  @TMP_vin varchar(17)

            DECLARE VinCursor CURSOR FOR
            select CustomerId,vehicleId,ManYear,Make,Model,vin from @VEH where Len(vin)>0
             OPEN VinCursor;
             FETCH NEXT FROM VinCursor
            INTO @MainCustomerId,@MainVehiclId,@TMP_ManYear,@TMP_Make,@TMP_Model,@TMP_vin;

          WHILE @@FETCH_STATUS = 0
          BEGIN
          delete  from @cuMerge;
          insert into @cuMerge
          select customerId,VehicleId from @VEH where
           VehicleId!= @MainVehiclId and Make = @TMP_Make and ManYear =@TMP_ManYear and Model= @TMP_Model and Len(vin)=0;

           if(@MainCustomerId>0 and @MainVehiclId > 0 and (select count(*) from @cuMerge)>0)begin
                    select @MainCustomerId as MaincustomerId,
                    substring ((select ',' + cast(customerId as varchar(20)) from @cuMerge where  customerId != @MainCustomerId for xml path('') ),2,8000 ) as oldCustomers,
                     @MainVehiclId as MainVehId,
                     substring ((select ',' + cast(vehicleId as varchar(20)) from @cuMerge where vehicleId!=@MainVehiclId  for xml path('') ),2,8000 ) as oldVehIds

           UPDATE dbo.Appointment set VehicleId = @MainVehiclId, customerId =@MainCustomerId where VehicleId in (select vehicleId from @cuMerge )
                    UPDATE dbo.RO set VehicleId = @MainVehiclId, customerId =@MainCustomerId where VehicleId in (select vehicleId from @cuMerge )
                    UPDATE VehicleInspection set vehicleId = @MainVehiclId where Vehicleid in (select vehicleId from @cuMerge )
                    UPDATE Campaign SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge)
                    UPDATE RFID SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge )
                    UPDATE VehicleAlert SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge)
                    UPDATE VehicleBackupLog SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge)

                    delete from dbo.vehicle where vehicleid in (select vehicleId from @cuMerge )
           end
          FETCH NEXT FROM VinCursor
          INTO @MainCustomerId,@MainVehiclId,@TMP_ManYear,@TMP_Make,@TMP_Model,@TMP_vin;
          END

          CLOSE VinCursor;
          DEALLOCATE VinCursor;

        FETCH NEXT FROM CUCursor
        INTO  @TMPdms_customerId;
        END

        CLOSE CUCursor;
        DEALLOCATE CUCursor;