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.

• Kasrak: Anyway this is a valuable comment, I liked it, Could you please give an example on the last part you mentioned ?
• palacsint: @Sypress: I'm sorry, I've tried to rewrite the query but I gave it up after a few tries. I haven't used so complex SQL queries recently. Please let me know how went the migration and what worked or not.
• Kasrak: no problem friend, thanks again, your suggestions could be useful to me.