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 |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2009-03-17 : 15:13:02
|
| Hi,I have to make a routine that imports data from database to a different database with different tables in each. That's the back story.Now, onto the problem: In case I mess something up, I'd like to undo inserts and updates. What I'd do is examine the dataset inserted, to ensure everything is exactly as I intended. Now, should I make an entirely separate routine which deletes the data I just inserted and/or updated? Or, should I use transaction or another method? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-17 : 15:27:01
|
| How big is the table you're inserting into? Sometimes it's easiest to just make a copy of the table and if you mess it up you just import the data from the copy or rename it.Mike"oh, that monkey is going to pay" |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-03-17 : 15:32:51
|
| Oh, no. This is a big project. We're talking copying into about 20 tables in one database from 2 or 3 tables in another. I shouldn't say copy, perhaps. Migrate might be a better term. It's not as simple as copying a value from a column in one database and putting into the other. The schema's of the two databases are completely different but the context of the data is the same. So, I have to analyze the tables in both databases and make some decisions.In any event, my original question was what's the best method for rolling back? Is a Transaction not applicable to inserts/updates of multiple tables? |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-17 : 16:03:45
|
| The problem with a transaction is you're holding up the works (usage of the table) when you leave it open and if it's not committed you have change to readuncommited to see what "would" be in there. You might be best served to create an intermediate table. Move your data over make sure it's pretty and then put it into the production table. It's an extra step and more room but probably a more sane approach.Mike"oh, that monkey is going to pay" |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-03-17 : 16:11:10
|
| Hmm, interesting. Okay, I will take your approach and run with it, especially the sane part.Thank you for your help! |
|
|
|
|
|
|
|
|
Undoing changes to a number of tables