Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Andre2
Starting Member
2 Posts |
 Posted - 2008-04-23 : 11:19:43
|
| I need to copy two large tables from one database into another, via the internet. I haven't worked out exactly how yet, but the first issue which has occurred to me is that by the time the first table has been exported (via a SELECT clause?) into a suitable file, the second table (to which it is related) will be out of sync. So, how do I ensure that I end up with a snapshot of the two tables, perfectly in sync with each other? I know that BEGIN/END TRANSACTION makes sure that UPDATES to tables remain in sync, but will it work just for SELECT statements? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-04-23 : 12:54:35
|
| Yes, but you need either the repeatable read isolation level, or the use of the HOLDLOCK hint. In the default isolation level (read committed) read locks are released as soon as the statement completes. Either of the above options will ensure the read locks are held til the end of the transaction. While the tables are locked, other connections will be able to read, but not add or update data.For total safety, I would suggest you use the TABLOCK hint as well.--Gail Shaw |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-23 : 13:12:06
|
| You can't copy data with just SELECT statements. So you are either using INSERT as well or some other export utility.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Andre2
Starting Member
2 Posts |
Posted - 2008-04-23 : 14:48:51
|
Thank you both very much for your helpful responses |
|
|
|
|
|
|
Use of Begin/End Transaction