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 |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2012-01-12 : 09:59:52
|
| I am loading huge Data from ServerA to ServerB. Planing is LinkedServerinsert into table select * from linkedserver.owner.table and every thing is done in Begin & END TransactionMy concern is the Transaction time and log.I have other option of using BCP, in this i have to move the file and access the files.I cannot use SSIS can some one suggest a better way to copy data by SQL or Pl SQL block.========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-12 : 12:41:30
|
| Don't do it in a transaction. If you need a transaction get all the data to #TempTables locally, and then process it into local tables within a transaction.If you need to be sure the source data is a snapshot (i.e. cannot change DURING the "pull" to your server) then that's more tricky.If its a one=off then perhaps BACKUP the database on the Remote, copy the Backup File to local, and then RESTORE to a temporary database and go from there ... |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-12 : 13:44:12
|
| Brett: I've told you a million times not to exaggerate! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-13 : 04:22:54
|
| Maybe you can enable DTC service and the use distributed transaction in the query.Something like thisBegin distributed transactionInsert QueryAfter Monday and Tuesday even the calendar says W T F .... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-13 : 05:19:10
|
| Yeah you probably could, but I wonder if its a good idea? depends on the circumstances of course ... but in my experience it tends to tie up all sorts of things, and such between-server actions take a while (unless you have stupendous infrastructure - we certainly don't!), and tend therefore to have undesirable consequences. Quick to program, annoying for the users ...But I'd appreciate your views, maybe I'm being over-sensitive for my users?!! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-15 : 04:54:49
|
| After I relooked at the OP's requirement I would rather say that setting up a local transaction would suffice since the OP is not trying to insert data into a remote server from the local server.So no need to use DTC at the first place.Also I would rather disagree with backup and restore solution.The biggest DB that I work with is about 2 TB having one table of 25 GB.So would be kind of impractical in my enviroment to do a backup and restore.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-15 : 08:23:10
|
quote:
Originally posted by Sachin.Nand
Also I would rather disagree with backup and restore solution.The biggest DB that I work with is about 2 TB having one table of 25 GB.So would be kind of impractical in my enviroment to do a backup and restore.
Yeah, massive database may well not work well with Backup / Restore ... but ... having said that, if you had poor quality COMMs between the servers doing:SELECT *INTO MyLocalTableFROM MyRemote_25GB_Table might KILL the remote system and the COMMs link ... |
|
|
|
|
|
|
|
|
Copy Data from ServerA to ServerB