Avatar of dev2dev
dev2dev
Flag for India

asked on 

SWITCH Command in Sql Server 2005

Hello,

I am trying to switch a non-partitioned table's data to a partitioned table. I am getting this error

Command:

ALTER TABLE NON_PARTITIONED SWITCH TO PARTITIONED_TABLE PARTITION 4

Error:

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'.

FYI:

(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 Partition
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

Thanks
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
dev2dev

8/22/2022 - Mon