SQL iteration-Insertion plus renaming

Question

Base Info

• Two tables: tData, tData2
• Exactly the same columns
• About 200,000 records
• SQL Server 2008 R2

Logic

At first sight we need to insert tData rows into tData2. What else?

We need a renamed version of a column inside another (tData2) with the condition checking it won't be an existing item when it's renamed. Here's the code:

INSERT INTO [tData2]
(
    [Key],
    Info1,
    Info2
)
SELECT 
    REPLACE([Key],'_',' '),
    Info1,
    Info2
FROM
    [tData]
WHERE
    (REPLACE([Key],'_',' ') = [Key]) OR
    (REPLACE([Key],'_',' ') NOT IN (SELECT [Key] FROM [tData]))

The problem is it's really slow for me on a top-notch 64 bit system. It has taken more than an hour so far and it's still executing.

How to speed it up? Any alternatives? Any ideas?

Answer 1

I'm not a DBA/DBMS guru, so just some ideas:

• Turn off indexes on tData2 during the migration.
• Do it in smaller chunks for smaller transaction log. (Batch commit on large INSERT operation in native SQL?, How to implement LIMIT with Microsoft SQL Server?)
• Make sure that [tData].[Key] has an index.
• I'd consider replacing the subselect with a join if it's possible at all.