I am trying to switch a non-partitioned table's data to a partitioned table. I am getting this error
ALTER TABLE NON_PARTITIONED SWITCH TO PARTITIONED_TABLE PARTITION 4
Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'NON_PARTITIONED_TABLE' allow values that are not allowed by range defined by partition 4 on target table 'PARTITIONED_TABLE'.
(Tables names or dummy, not actual names)
Non-Paritioned table's structure matches exactly with the partitioned table
I copied table description using sp_help into excel of both tables and did comparision, which is matching 100% (column names, data types, null ability and so on,indexes)
In fact, we have a stored procedure which clones partitioned table using schema_information views (that way it matches 100%)
Only 1 difference is that filegroup is different, I think this doesn't matter, or am I wrong?
Please help me to understand where and what is going wrong?
Additional info: ETL flow
Create FileGroup and Files
Clone all existing tables with a prefix TMP_ on newly created FileGroup
BCP into newly created TMP_ tables
Create indexes on TMP_ (so that to table structure's match 100% which enables switching)
Switch tables --- And here is the error I get
Drop TMP_ tables