Getting rid of partitioning in a table, how to merge partitions in a loop?

Question

SQL Server

I use MS SQL Server 2012.
1. made filegroups
2. made files
3. created a function and partitioning scheme
I made 4 boundary values, which you can get rid of by calling:
MERGE RANGE ()
you can make this call as many times as there are so many partitions. What if there are many partitions? I wanted to do it in a loop, but when the query is executed, only 1 or 2 sections are merged and there are no errors, to combine the remaining ones, you need to run the script again:

-- Объединяем секции
BEGIN TRY
  -- открываем тразакцию 
  BEGIN TRAN	
  -- Для перебора граничных значений используем курсор
  DECLARE boundaryCursor cursor
  FOR
    --получаем граничные значения
    SELECT r.value FROM sys.tables AS t
    JOIN sys.indexes AS i
      ON t.object_id = i.object_id
    JOIN sys.partitions AS p
      ON i.object_id = p.object_id AND i.index_id = p.index_id 
    JOIN  sys.partition_schemes AS s 
      ON i.data_space_id = s.data_space_id
    JOIN sys.partition_functions AS f 
      ON s.function_id = f.function_id
    LEFT JOIN sys.partition_range_values AS r 
      ON f.function_id = r.function_id and r.boundary_id = p.partition_number
    WHERE t.name = 'syn_PrimaryDocuments' AND i.type <= 1

  OPEN boundaryCursor
    DECLARE  @counter int
    DECLARE @boundary sql_variant
    SET @counter = 0
    fetch next from   boundaryCursor INTO @boundary
    WHILE @@FETCH_STATUS = 0
    BEGIN 
      SELECT  @boundary
      IF @boundary IS NOT NULL
      BEGIN
        --Начало объединения по границе @boundary
        IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'stateFunction')
        BEGIN
          ALTER PARTITION FUNCTION stateFunction ()
          MERGE RANGE (CAST(@boundary as int))
        END
      END
      -- Конец объединения по границе @boundary
      SET @counter = @counter + 1
      fetch next from   boundaryCursor INTO @boundary
    END
  CLOSE boundaryCursor
  DEALLOCATE boundaryCursor
  --Завершаем тразакцию в случае успеха
    COMMIT TRAN
END TRY
BEGIN CATCH
  --Откатываем 
  ROLLBACK TRAN
  CLOSE boundaryCursor
  DEALLOCATE boundaryCursor
    --Получаем описание ошибок
   SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Is it possible to do the union in a loop?

Answer 1

I didn't understand why. To get rid of partitioning, it is enough to recreate the PRIMARY KEY on the main partition (not the partitioning scheme). Why combine values ​​then?