Yes, the target table has a identity column, the said "disable" identity column means I set the identity column No under the table's identity specification.
actually, the indentity column is not important as the table has a unique field. thank you Jeff Moden!
Actually, Identity columns are incredibly important when making copies of tables and their data because there's a nasty little bug in SQL Server. IF you have to use SET IDENTITY_INSERT ON to insert into the target table, then ALL the rows you're inserting will materialize in TempDB to be sorted and that sorry little but totally insane fact will be the cause of HUGE TempDB usage especially since MS compounded that with another problem by permanently making all data files in TempDB to grow at the same rate instead of it being optional like before (through Trace Flag 1117). (Sidebar: MS totally screwed us and a lot of my large ad hoc copy processes when they did all this proving once again that "Change is inevitable... change for the better is NOT".).
Getting back to your post above, setting the IDENTITY column option to "No" on the target table means that you no longer have an IDENTITY column in the target table. Yes, the named column still exists but it no longer carries the IDENTITY property and that's a good thing here.
With that being said, you have indexes on both the source table and the target table. I'm assuming that both sets of indexes are identical. That would also mean that both indexes have the same Clustered Index and that's a very good thing because it will allow us to copy to the target table without a sort (which we'll include to be safe but should not actually materialize in the execution plan) and still allow us to get "minimal logging".
So, all that being said, here's what I'd do to copy data from the source table to the target table WITH the Clustered Index in place on the target table and still be able to get minimal logging and avoid a Clustered Index Build or Rebuild.
- Disable all Non-Clustered indexes (NCIs from here on). If any of them carry the UNIQUE attribute, you'll end up with FK problems if any FK's are pointing at those underlying columns. We'll need to take an extra steps if that's true but I'm making the assumption for now that you don't have any such NCIs. To be honest, I normally DROP the NCI's just to make sure. You can rebuild them later.
- Make sure that the database the target table is in is either in the BULK LOGGED or SIMPLE Recovery Models. Keep in mind that the SIMPLE Recovery Model will break the log file chain if it exists and you'll need to do a DIF backup or FULL backup to reestablish it if it existed prior to all of this. Such backups would be done once we're done with the entire copy process.
- TRUNCATE the target table. DELETE WILL NOT WORK HERE. It HAS to be a TRUNCATE and it HAS to be AFTER you've disabled all the Non-Clustered Indexes (and I wait until after any changes to the Recovery Model just to be sure). Don't even think you can use Trace Flag 610 to trick SQL Server into being able to do minimal logging on the NCI's because the actual chances of it working are slim to none and slim just left.
- Do the copy with an INSERT/SELECT that following the rules for "Minimal Logging". This will make the copy nearly twice as fast as most other methods because of the seriously reduced log file activity and you won't have to rebuild the Clustered Index, which saves a huge amount of space and time. Here's what the code should generally look like... of course, you'll need to change some table and column names...
INSERT INTO dbo.TargetTable WITH (TABLOCK) -- The Tablock is critical for Minimal Logging
(columnlisthere) -- If the all columns in both tables are in the same order, can remove this line.
SELECT columnlisthere -- If the all columns in both tables are in the same order, can be "*"
FROM dbo.SourceTable -- You could use WITH (TABLOCK) here to prevent time used for lock escalation.
ORDER BY keycolumnsofclusteredindex -- Required for "safety" but probably won't be used in execution plan.
OPTION (RECOMPILE) -- Undocumented in BOL but essential in some cases, so do it all the time.
- Once step 4 is completed, either rebuild your disabled NCI's or recreate them if you dropped them. In the BULK LOGGED or SIMPLE Recovery Models, these will also be minimally logged and surprisingly fast.
- If you were in the FULL (or BULK LOGGED) recovery models and you switched it to SIMPLE at the beginning of all this, then you'll need to switch to the FULL Recovery Model, do a DIF or FULL backup to restart the log file chain. If you started out in the BULK LOGGED Recovery Model, you can then switch back to that and you're done.
Although I'm doing all of that from memory, I've done it enough times where I don't believe I've missed anything. This is what I do with quarter Terabyte tables or when creating a shedload of test data.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)