1

Apologies for perhaps a basic question, but I've genuinely tried my own research but I cannot even find the correct syntax for this question, and so I am not finding the solution.

I have a database with a number of tables, columns, rows etc in an online system. I have made a massive update to the system offline, and it is ready to go live this week. However, some of the tables have been altered, some new tables have been added etc, as a result of new features within the system.

My question is: Can I update my database, with the heavily altered tables, but not delete the previous information on it? For example: Say I have a table in the old system called Users, which tracks Username, and Password. But I've updated the system, and now the Users tables tracks Username, Password, Name, Age, Date of Birth, Points ... whatever, a lot more information than before.

What I want to happen, is that I update the table (and overall database) so that the new columns and tables etc appear, but I do not lose the information from the previous iteration. So I would not have to re-enter password/username etc for Users.

When I search this question, all I get is answers saying I can manually enter the new tables/columns, or mistakenly it thinks I'm asking about concatenation of values, but this is not my intention.

Ideally, I would like an IMPORT of the new database INTO the old database.

Any help would be very much appreciated.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
cmprogram
  • 1,854
  • 2
  • 13
  • 25

2 Answers2

2

First, I strongly recommend you make a backup copy of your production data and keep that handy. You will restore it many times to get back to the "original" state while you develop your data-integration.

Restore the backup copy to a distinct database on your local MySQL Server instance. You will want to do a lot of tests as you learn how to do the data integration, and some of these will be destructive.

The process will be to run a bunch of scripted changes against your local copy of production database, then see if it worked the way you want it to. If it didn't, drop and restore the local copy of the production database, correct any mistakes in your data integration steps, and try again. This may take days to get right, and many repeated executions.

So you have two tasks:

  1. Upgrade the table structure of your production database to match that of your locally altered database. You can use ALTER TABLE to make many such changes.

    This is a complex task, because it can be hard to analyze two different tables and figure out the minimal set of ALTER TABLE statements needed to upgrade one to be like the other. One of the MySQL utilities can help with this: mysqldiff. You can use it to generate the ALTER TABLE statements needed.

  2. Sync your local data into the production database. This is complex because you have some data that should partially overwrite the existing production data. You should learn how to use REPLACE and INSERT...ON DUPLICATE KEY UPDATE.

    See my answer to “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE” for some useful explanations on that.

    You can also do UPDATE statements to join tables from different databases. This will be helpful when you want to replace data based on a common value in some column, but the column isn't UNIQUE, so you can't use REPLACE.

This is going to be a hard, meticulous process. Most professional software development projects struggle with this. They have to be very careful to script every change to their tables, and they use "schema migration" tools to apply the incremental changes in order against any database, so the target database can be upgraded to any revision.

But trying to figure out all those incremental changes after you have done a bunch of them in development is like trying to un-bake a cake.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Since you running mysql server, you could just write a query to import the data.

CREATE TABLE [dbo].[oldTable](
    [Pass] [varchar](50) NULL,
    [Username] [varchar](50) NULL
) ON [PRIMARY]

Insert into oldTable (Pass,Username)
Select 'pass', 'user'

CREATE TABLE [dbo].[newTable](
    [Pass] [varchar](50) NULL,
    [Username] [varchar](50) NULL,
    [OtherColumn] [varchar](50) NULL
) ON [PRIMARY]

Insert into newTable (Pass,Username)
Select 'pass2', 'user2'
Emka
  • 340
  • 6
  • 16
  • This answer uses Microsoft SQL Server syntax, in spite of acknowledging the OP is using MySQL. It's really common for Microsoft users to not understand that other database products exist, or that other products have different syntax. – Bill Karwin Nov 02 '17 at 15:52