No Problem adding PK to BKUP table. The duplicate check query you said to try, which I've already run myself on the original table and backup table, produces no results.

Here is the script (note: the DDL for the table creation is midway thru the script. You can copy and paste this to a new window to create the initial table. Just change to filegroup to PRIMARY.):

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

SET ANSI_PADDING ON;

DECLARE

@TableName VARCHAR(128)

, @SQL VARCHAR(300)

-------------------------------------------------------------------------------

-- Backup the STUDENT_ATTENDANCE table to STUDENT_ATTENDANCE_BKUP

-------------------------------------------------------------------------------

USE zPartitionTest;

-- first let's make a backup of the student_attendance table

IF NOT EXISTS(select 1 from sys.tables where [name] = 'STUDENT_ATTENDANCE_BKUP')

BEGIN

PRINT 'Copying student_attendance table to student_attendance_bkup table';

SELECT * INTO STUDENT_ATTENDANCE_BKUP FROM STUDENT_ATTENDANCE;

END

IF NOT EXISTS(select 1 from sys.tables where [name] = 'STUDENT_ATTENDANCE_BKUP')

PRINT ' ***** Table copy failed; STUDENT_ATTENDANCE_BKUP does not exist. Script stopped. *****';

ELSE

BEGIN --bkup table exists.

IF EXISTS(select 1 from sys.tables where [name] = 'STUDENT_ATTENDANCE')

BEGIN

PRINT 'Dropping table STUDENT_ATTENDANCE';

DROP TABLE STUDENT_ATTENDANCE;

END

USE master;

-----------------------------------------------------------------------

-- Create new database filegroup, new data file in the new filegroup,

-- Partition Function and Partition Scheme...

-----------------------------------------------------------------------

IF EXISTS(select 1 from sys.master_files where [name] = 'zPartitionTest_ARCHIVE.NDF')

BEGIN

PRINT '...dropping the old data file, zPartitionTest_ARCHIVE.NDF.'

ALTER DATABASE zPartitionTest

REMOVE FILE zPartitionTest_ARCHIVE;

END

IF EXISTS(select 1 from sys.filegroups where [name] = 'DatabaseArchivePartition')

BEGIN

PRINT '...dropping the old filegroup, DatabaseArchivePartition.'

ALTER DATABASE zPartitionTest

REMOVE FILEGROUP DatabaseArchivePartition;

END

PRINT 'Adding filegroup DatabaseArchivePartition.'

ALTER DATABASE zPartitionTest

ADD FILEGROUP DatabaseArchivePartition; -- separate filegroup to hold the archive table partitions

PRINT 'Adding new data file, zPartitionTest_ARCHIVE.NDF, to the new archive partition filegroup.'

ALTER DATABASE zPartitionTest

ADD FILE

( NAME = zPartitionTest_ARCHIVE

,FILENAME = 'G:\data\SQLSERVER\zPartitionTest_ARCHIVE.NDF'

,SIZE = 1024 MB

,FILEGROWTH = 1024 MB

)

TO FILEGROUP DatabaseArchivePartition;

USE zPartitionTest;

IF EXISTS(select 1 from sys.partition_schemes where [name] = 'STUDENT_ATTENDANCE_PRTN_SCHM')

BEGIN

PRINT '...dropping Partition Scheme, STUDENT_ATTENDANCE_PRTN_SCHM'

DROP PARTITION SCHEME STUDENT_ATTENDANCE_PRTN_SCHM

END

IF EXISTS(select 1 from sys.partition_functions where [name] = 'STUDENT_ATTENDANCE_PRTN_FNCTN')

BEGIN

PRINT '...dropping Partition Function, STUDENT_ATTENDANCE_PRTN_FNCTN'

DROP PARTITION FUNCTION STUDENT_ATTENDANCE_PRTN_FNCTN

END

PRINT 'Creating the partition function, STUDENT_ATTENDANCE_PRTN_FNCTN, that will be used to split out the archive data from the student_attendance table.'

CREATE PARTITION FUNCTION STUDENT_ATTENDANCE_PRTN_FNCTN (DATETIME)

AS RANGE RIGHT FOR VALUES ('2013/08/01 00:00:00.000'); -- all data less than this date goes into the first partition (archive).

PRINT 'Creating the partition scheme, STUDENT_ATTENDANCE_PRTN_SCHM, to specify where the archive data will be stored.'

CREATE PARTITION SCHEME STUDENT_ATTENDANCE_PRTN_SCHM

AS PARTITION STUDENT_ATTENDANCE_PRTN_FNCTN

TO (DatabaseArchivePartition, [PRIMARY]); -- old data to archive, new data to primary.

PRINT 'Creating new STUDENT_ATTENDANCE table, specifying how to partition data.'

CREATE TABLE [dbo].[STUDENT_ATTENDANCE]

(

[ATT_OID] [nchar](14) NOT NULL

,[ATT_STD_OID] [nchar](14) NULL

,[ATT_SKL_OID] [nchar](14) NULL

,[ATT_DATE] [datetime] NOT NULL

,[ATT_CODE_VIEW] [varchar](50) NULL

,[ATT_TIME_VIEW] [varchar](40) NULL

,[ATT_ABSENT_IND] [char](1) NULL DEFAULT(0)

,[ATT_TARDY_IND] [char](1) NULL DEFAULT(0)

,[ATT_DISMISSED_IND] [char](1) NULL DEFAULT(0)

,[ATT_REASON_CODE] [varchar](20) NULL

,[ATT_EXCUSED_IND] [char](1) NULL DEFAULT(0)

,[ATT_PORTION_ABSENT] [numeric](8, 4) NULL

,[ATT_OTHER_CODE] [varchar](20) NULL

,[ATT_OTHER_CODE_02] [varchar](20) NULL

,[ATT_COMMENT] [varchar](max) NULL

,[ATT_TIMESTAMP] [numeric](14, 0) NULL

,[ATT_FIELDA_001] [varchar](10) NULL

,[ATT_FIELDA_002] [varchar](10) NULL

,[ATT_FIELDB_001] [varchar](25) NULL

,[ATT_LAST_MODIFIED] [numeric](14, 0) NULL

,[ATT_ABSENT_IND_02] [char](1) NULL DEFAULT(0)

,[ATT_TARDY_IND_02] [char](1) NULL DEFAULT(0)

,[ATT_DISMISSED_IND_02] [char](1) NULL DEFAULT(0)

,[ATT_REASON_CODE_02] [varchar](20) NULL

,[ATT_EXCUSED_IND_02] [char](1) NULL DEFAULT(0)

,[ATT_COMMENT_02] [varchar](max) NULL

,[ATT_RESPONSE] [varchar](max) NULL

,[ATT_RESPONSE_02] [varchar](max) NULL

)

ON STUDENT_ATTENDANCE_PRTN_SCHM(ATT_DATE); --partition on att_date

-- Check for clustered index and primary key

SELECT @TableName = ISNULL(o.[name], '')

FROM sys.indexes i

JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE i.[name] = 'CX_STUDENT_ATTENDANCE_ATT_DATE' ;

IF @TableName <> ''

BEGIN

PRINT '...dropping the old CX_STUDENT_ATTENDANCE_ATT_DATE constraint'

SET @SQL = 'DROP INDEX CX_STUDENT_ATTENDANCE_ATT_DATE ON ' + @TableName

EXEC(@SQL);

END

PRINT 'Adding Clustered Index on ATT_DATE.'

CREATE CLUSTERED INDEX CX_STUDENT_ATTENDANCE_ATT_DATE

ON STUDENT_ATTENDANCE(ATT_DATE);

SELECT @TableName = ISNULL(o.[name], '')

FROM sys.indexes i

JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE i.[name] = 'PK_STUDENT_ATTENDANCE_ATT_OID'

IF @TableName <> ''

BEGIN

PRINT '...dropping the old PK_STUDENT_ATTENDANCE_ATT_OID constraint'

SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT PK_STUDENT_ATTENDANCE_ATT_OID'

EXEC(@SQL)

END

PRINT 'Adding non-clustered Primary Key on ATT_OID'

ALTER TABLE [dbo].STUDENT_ATTENDANCE_BKUP

ADD CONSTRAINT PK_STUDENT_ATTENDANCE_BKUP_ATT_OID PRIMARY KEY NONCLUSTERED (ATT_OID)

ON [PRIMARY];

PRINT 'Copying data from Staging table into the new partitioned STUDENT_ATTENDANCE table.' + CHAR(13);

INSERT INTO STUDENT_ATTENDANCE SELECT * FROM STUDENT_ATTENDANCE_BKUP; --PRIMARY KEY VIOLATION ERROR

--check partition counts

SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='STUDENT_ATTENDANCE';

SELECT $PARTITION.STUDENT_ATTENDANCE_PRTN_FNCTN(ATT_DATE) AS PARTITIONID,

COUNT(* ) AS ROW_COUNT

FROM DBO.STUDENT_ATTENDANCE

GROUP BY $PARTITION.STUDENT_ATTENDANCE_PRTN_FNCTN(ATT_DATE)

ORDER BY PARTITIONID;

END --bkup table exists.

SET ANSI_PADDING OFF;