Quickest / easiest way to move data between 2 columnstore tables.

Question

I have current and archive fact tables. Both are columnstore with the latter having archive compression. I want to keep 4 full calendar years in the current fact so at each new year, I will move all the data from the oldest year to the archive.

Both are partitioned by Posting month with a different filegroup for each calendar year.

From what I've read, I'm not going to be able to use partition switching because they have to be in the same filegroup. I'm sure that the different compression level is also an issue.

So, I think I'm stuck with simply copying from current to archive then dropping the source partition. Unless somebody else has a better method?

Answer 1

You can use partition switching. You just need to put the tables on the same (or compatible) partition schemes.

The Archive table would have empty partitions corresponding to the non-empty partitions of the Current table. When you switch a partition from Current table to the Archive table it stays on the same FileGroup.

Then at some point rebuild the archive partition with archival compression.

Otherwise INSERT...SELECT followed by TRUNCATE TABLE WITH (PARTITIONS ...) is best.

Answer 2

Hi @Nick Ryan ,

...Unless somebody else has a better method?

Whether the tables are columnstore ones or not,you can always use the statement: insert into... select ...from ...

Try insert-into-select-statement as next:

INSERT INTO DestinationTable (Column1,Column2,..., ColumnN) SELECT * FROM SourceTable

Code on myside as this, change to your table names on your side:

---move data from demo1 to demo (move the last 1 row from demo1 into demo)  
INSERT INTO HumanResources.Employee_Demo (NationalIDNumber,LoginID,JobTitle,BirthDate, MaritalStatus,Gender,HireDate,VacationHours)  
SELECT * FROM  
    HumanResources.Employee_Demo1  
where LoginID= 'adventure-works\wanida0'  

Test code:

Use AdventureWorks2017  
GO  
  
--create table Demo and Demo1  
SELECT TOP (10) [NationalIDNumber]  
      ,[LoginID]  
       ,[JobTitle]  
      ,[BirthDate]  
      ,[MaritalStatus]  
      ,[Gender]  
      ,[HireDate]  
      , [VacationHours]    
      INTO   [AdventureWorks2017].[HumanResources].[Employee_Demo]       
  FROM [AdventureWorks2017].[HumanResources].[Employee]  
  
    
SELECT TOP (20) [NationalIDNumber]  
      ,[LoginID]  
       ,[JobTitle]  
      ,[BirthDate]  
      ,[MaritalStatus]  
      ,[Gender]  
      ,[HireDate]  
      , [VacationHours]    
      INTO   [AdventureWorks2017].[HumanResources].[Employee_Demo1]       
  FROM [AdventureWorks2017].[HumanResources].[Employee]  
  
  
select * from HumanResources.Employee_Demo  
  
select * from HumanResources.Employee_Demo1  
  
--Check whether they are columnstore table  
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID) SchemaName,  
 OBJECT_NAME(OBJECT_ID) TableName,  
 i.name AS IndexName, i.type_desc IndexType  
FROM sys.indexes AS i   
WHERE is_hypothetical = 0 AND i.index_id <> 0   
 AND i.type_desc IN ('CLUSTERED COLUMNSTORE','NONCLUSTERED COLUMNSTORE')  
GO  
  
--create columnstore index  
CREATE CLUSTERED COLUMNSTORE INDEX cci_Employee_Demo ON HumanResources.Employee_Demo;    
GO    
  
CREATE CLUSTERED COLUMNSTORE INDEX cci_Employee_Demo1 ON HumanResources.Employee_Demo1;    
GO    
  
---move data from demo1 to demo (move the last 1 row from demo1 into demo)  
INSERT INTO HumanResources.Employee_Demo (NationalIDNumber,LoginID,JobTitle,BirthDate, MaritalStatus,Gender,HireDate,VacationHours)  
SELECT * FROM  
    HumanResources.Employee_Demo1  
where LoginID= 'adventure-works\wanida0'  

More information: sql-server-insert-into-select

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.