I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure and schema intact.
select * into [dbo].[new_Non_partitioned_Table] from [dbo].[partitioned_table]
I then dropped the partitioned table and renamed the new table. However, it caused me to lose the data structure and some dependency (FK) that forced me to drop constraints. This is for production and loss of data is not an option.
Is there a better way to remove a partition from an existing table?