1

I need to change data in three tables (update some existing rows, add some new, delete some old). I need it to be done in one moment. Problem is that data need to be changed manually and probably it will take some time to get it done. So I'm going to use beta server in order to make the changes. Problem is: how to update production server with data from another database?

My solution : Dump data from beta server and restore it on production.
Flaws : I would have to delete all data on production first and it's problematic because of foreign keys (I could turn off keys first, but is there a way to avoid it?).

I found similar question, where one of the answers suggests using dblink command. I think I could write update statement, but this still seems to be a bit an overkill.

Edit (additional explanation):
There is the production server (let's call it Production) and there is development server (let's call it Beta). So I need to have some data changed on Production (3 tables that are interconnected and they are also referenced from other tables in DB). To be precise - these tables hold learning programme - topics, topics' groups and subtopics. There are registers that refer to these elements. But I need to have these changes done in one moment (meaning: through SQL script). In order to accomplish that I going to use the Beta server - which holds a copy of production DB (done in certain moment, no real time synchronization). So I will have data updated in the 3 tables on Beta server and I need to move this data to Production.

Community
  • 1
  • 1
Wiktor
  • 2,909
  • 4
  • 17
  • 23
  • Can you please explain the Question with more details? Ex: The number of tables you want to change. Is the data that you want to include present in another database in the same table structure? More details please. – RGV Jun 04 '13 at 19:59
  • Is this question about (i) a tool to make the process of changing values in the cells easy (eg. a fron end that will show you cells in logical groupping, where you can go up and down the table or apply a filter to find a row you need to change) `OR` (ii) a way to sync prod database after the changes have been made to its copy given to your client? – Stoleg Jun 10 '13 at 07:56
  • @Stoleg, I can't really see the difference - I need to have job done :) – Wiktor Jun 10 '13 at 09:18
  • I trying get the full picture of your "job". Is it making changes, or syncing two DBs after changes are made? – Stoleg Jun 10 '13 at 09:50

2 Answers2

2

What we normally do when we're maintaining a production database is to write a SQL script to do the updates, deletes, setc. We test it against Beta, which should be a pretty good copy of Production.

When we're sure it's doing the right thing, we run it against production.

We don't carry data forward from a non-production environment into production. Production is the master of the data, and taking a copy, doing the update, then trying to load it back in is hard and scary (as you've already figured out).

This mechanism allows us to ensure that a tested script is run, that will make known updates into production. It eliminates human error, because all you have to get right when you're connected to production is the name of the script.

GregHNZ
  • 7,946
  • 1
  • 28
  • 30
  • I agree that this is a good way of doing thing, but I think situation here is a bit different, cause changes that need to be done must be done by client - I cannot expect client to write SQL scripts :) – Wiktor Jun 08 '13 at 10:28
  • You should have said it straight away that it is **not you**, who will be making the changes!!! – Stoleg Jun 08 '13 at 10:41
  • Yes, our situation often works like that too - in many customers we don't have access to production. We write the scripts based on the data in a test system that we have access to and what we know about how this data relates to the production data. We then give the scripts to the customer and say "run this". Instructions are sometimes required on how to run a script, but it reduces the risk of them making typing mistakes. – GregHNZ Jun 09 '13 at 04:45
  • 1
    @Wiktor: in that case you can let the client do the changes on your `Beta`, and then compare the two databases using a tool such as SQL Compare. I believe it's possible for such tools to generate an "update" script that you can then run on `Production`. – Artyom Jun 10 '13 at 15:25
  • @Artyom, I need such tool for data comparison - not schema. – Wiktor Jun 10 '13 at 15:42
  • @Wiktor: Oops, I meant SQL Data Compare which is for data. – Artyom Jun 10 '13 at 15:44
1

Your strategy depend on your ability to identify uniquely ever row.

If you can create a unique key to every row (not necessary that you have created a UNIQUE constrain), then:

  1. Copy all data to Beta. Do your changes by marking affected rows or creating NEW,UPDATEd and DELETEd tables.
  2. Copy marked rows only to Production.

  3. Apply changes. If you can find out that Production rows had been changed since your copy - decide ho to deal with it.

    Insert INTO Pro_Table SELECT * FROM New_Rows_Table

    UPDATE Prod_Table set Prod_Col_1 = Upd_tbl_c1, Prod_Col_2 = Upd_tbl_c2, ... , Prod_Col_N = Upd_tbl_cN from Update_Table where Prod_Table.RowKey = Update_Table.RowKey

    DELETE FROM Prod_Table FROM Delete_Table where Prod_Table.RowKey = Delete_Table.RowKey

This is SQL Server code style, but you can get the idea and make necessary changes. For example Delete_Table should have RowKey column only.

If you can NOT create a unique key. You have some options:

Number all your rows by adding a new column and populating it with Row_Number() function.

OR

Lock your database for any changes or do not apply them and store separately. After your changes promoted to Production, apply deffered changes.

OR

Capture all INSERT , 'UPDATE' and DELETE statements on Production database using triggers. You need Inserts as they may be affected by later Updates and Deletes.

When making changes on Beta copy rows you change manually on Beta into separate table(s).

After your changes are done, review captured data from Production and decide how to apply them to rows you've changed and copied from Beta.

To avioid problems with foreign keys, copy / import your updated table under a different name. Rename original table and then new one. Drop original table. This way you do not delete any data and all foreign keys are enforced all the time.

Stoleg
  • 8,972
  • 1
  • 21
  • 28
  • Thanks for that answer, but it still seems like a complicated solution... I'm looking for some simple solution or information that there is no such :) – Wiktor Jun 06 '13 at 06:31
  • How would you define "simple"? You can also create a remote coonection from Access db and edit production directly... – Stoleg Jun 06 '13 at 07:53
  • By "simple" I mean solution which complexity is let's say 10 times smaller than doing things manually row by row ;) – Wiktor Jun 08 '13 at 10:31
  • How do you measure smaller - time or mouse clicks? )) What exactly is complicated? How many rows do you need to edit? – Stoleg Jun 08 '13 at 10:37
  • If you need to edit up to a 60k rows, then copy them in Excel, make changes and for each row create an `UPDATE` statement with `Concatenate` function. Then copy paste and run on prod – Stoleg Jun 08 '13 at 10:39