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]
truncate table [IDAT_MR_NOTEHISTORY]
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_MR_NOTEHISTORY]') AND name = N'PK_iMR_NOTES')
USE [101687_test2]

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

--hack to do a do-while loop in sql
while 1 = 1
    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
    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)

    set @ChunkCounter = @ChunkCounter + @ChunkSize

USE [Cvti101687]
    [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'

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?

closed as off-topic by Max Vernon, Paul White, RolandoMySQLDBA, Taryn, Jon Seigel Oct 2 '13 at 23:38



  • 1
    Could it be that you remove the index in database Cvti101687 and you insert into a table in database cvti101639. – Mikael Eriksson May 4 '12 at 6:02
  • @MikaelEriksson Wow, thanks for catching the typo. Let me fix it and see what happens – Scott Chamberlain May 4 '12 at 6:05
  • @MikaelEriksson that was it, thanks! Can you post it as a answer so I can give you the credit. – Scott Chamberlain May 4 '12 at 6:08
  • You can query sys.objects too for a primary key – gbn May 4 '12 at 6:48

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