After dropping the clustered index, it is still attempting to do a clustered insert

Question

I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows.

I am running the following script to do it:

USE [Cvti101687]
go
truncate table [IDAT_MR_NOTEHISTORY]
go
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_MR_NOTEHISTORY]') AND name = N'PK_iMR_NOTES')
    ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] DROP CONSTRAINT [PK_iMR_NOTES]
GO
USE [101687_test2]
GO

declare @ChunkCounter int
declare @ChunkSize int
set @ChunkCounter = 0
set @ChunkSize = 50000

--hack to do a do-while loop in sql
while 1 = 1
begin
    insert into cvti101639..IDAT_MR_NOTEHISTORY with (tablock)
    SELECT newid(), '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000'
    ,'00000000-0000-0000-0000-000000000000',    ISNULL(TargetClientAccountNum, [REC_CLINUM]), ISNULL(TargetPetAccountNum, '     ')
    ,ISNULL(pck_desc, 'Converted Medical Record'), isnull([REC_DOCTOR], '99999'), isnull([REC_ENTRY],''), isnull(REC_ENTRY,''),[rec_dattim], 0
    FROM [RECORD]
    left join [WVSSPCK] on pck_code = 99 and pck_link = [REC_TAG]
    left join cvti101639..idat_patients p on p.oldAccountNum = REC_CASE
    where rec_deleted = 0 and (rec_rtype = 0 or rec_rtype is null)
        and (TargetClientAccountNum is not null or [REC_CLINUM] is not null)
        and rec_id >= @ChunkCounter and rec_id < @ChunkCounter + @ChunkSize

    --If no rows inserted, break out of the loop
    if(@@rowcount = 0)
        break;

    set @ChunkCounter = @ChunkCounter + @ChunkSize
end

go
USE [Cvti101687]
go
ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] ADD  CONSTRAINT [PK_iMR_NOTES] PRIMARY KEY CLUSTERED 
(
    [cvtGUID] ASC
)

To speed things up, I attempted to drop the clustered index on the destination table and rebuild it at the end (Yes, I know it's a clustered index on a GUID, I am just the conversions guy, I don't get a say in how the database is designed).

However, when looking at the actual execution plan from one of the iterations of the while loop of the insert, I see that it is performing a Clustered Index Insert on my index that I already dropped. Furthermore, the sort it is performing before the clustered index insert costs 69% of each chunk.

  |--Clustered Index Insert(OBJECT:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[PK_iMR_NOTES]), SET:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtGUID] = RaiseIfNull([Expr1012]),[Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtClientGUID] = [Expr1013],[Cvti
        |--Parallelism(Gather Streams, ORDER BY:([Expr1012] ASC))
             |--Sort(ORDER BY:([Expr1012] ASC))
                  |--Compute Scalar(DEFINE:([Expr1012]=newid(), [Expr1013]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1014]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1015]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1016]={guid'
(snip)

Here is a copy of the actual execution plan after it has completed the first few iterations of the chunking loop. Just take the xml and save it as a .sqlplan file and it should be able to be opened in Management Studio.

What am I doing wrong that is causing this not doing a heap insert?

Answer 1

You are removing the primary key in database Cvti101687 and inserting to a table in database cvti101639.