We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to mention NOLOCK during SSIS import and export wizard

Medium Priority
1,555 Views
Last Modified: 2013-11-10
Hello experts,

I wanted to copy around 20 tables from one db to another using import and expoert wizard but without locking the source tables. Is there any option inside the wizard where in i can check that option or any other alternative?
Thanks
Comment
Watch Question

Copying entire tables can't not lock the source because of race conditions that could occur. If the table contents were copied over, and a new entry was made after the copy, and then the index was copied over, where the index has a reference to the new entry which wasn't copied over, then the copied table and index is corrupt. This is the only obvious example I can readily think of as to why the source tables need to be locked for the copy.

--
Alain

Commented:
You could select * Into MigrateTable1 From Table1 for each of the 20 or so tables and then use the Import/Export, or you can link the db's and do the same as above directly from one to the other.

Author

Commented:
@alain - in my case i am copying whole table so u mean to say there wouldn't be any lock. i am testing it now and so far there is no lock and it still has to transfer around 100k rows
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes it worked, sorry for the late response, been busy..
Thanks!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.