Fast Import Data with xml column

Question

Fast Import Data with xml column

psred

SSCrazy

Points: 2049
More actions
November 13, 2016 at 4:20 pm

#322843

Can anyone tell me the quick way to copy a source table of 1.3Tb data to destination table with xml datatype column in source table.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

psred (11/13/2016)
Can anyone tell me the quick way to copy a source table of 1.3Tb data to destination table with xml datatype column in source table.

Can you elaborate further on this and provide all the relevant details such as the Source Database Size, the Destination Database Details etc.? The fact that it has an XML column only tells us that there will be lots of LOBs but that is almost irrelevant to the copy process.

😎

psred SSCrazy Points: 2049 More actions · Answer 2

Source db is 2Tb and destination table is also on same server but each dbs are on different drives.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

psred (11/13/2016)
Can anyone tell me the quick way to copy a source table of 1.3Tb data to destination table with xml datatype column in source table.

Will the two tables need to be kept in sync? If so, just use a synonym instead. If the two tables are required to contain different data, then you have a whole lot more to worry about than just speed. You have a log file explosion getting ready to happen, which also slow things down a whole lot.

To avoid a lot of that, set the target database to BULK LOGGED, create a table WITH the same Clustered Index as the source, and use "Minimal Logging" techniques to copy the data in the same order as the Clustered index on both tables. You should also look into Trace Flag 610 but I'd avoid putting any non-clustered indexes on the table until all the database was copied. It would also be better to chunk the data (perhaps roughly 10 or 20GB at a time) so that if something goes wrong, you won't have to start over completely.

The addition of NCIs after all the data is copied should also be done during BULK LOGGED recovery model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)