Data migration issue

Feb 18, 2013 at 12:49 PM
What happen when I use SQLAzureMW to migrate data from my local db to exiting Azure db. Existing DB already has some data. Is SQLAzureMW update existing data or delete existing one and then upload whole data again.
Coordinator
Feb 18, 2013 at 9:13 PM
Hi,

SQLAzureMW generates scripts that basically checks if table does exist. If the table does not exist, then it creates the table otherwise it does nothing. Thus if you have a table with data in it, SQLAzureMW will not modify it in anyway. It will try to upload data to that table via BCP. If all of the new data being uploaded does not exist, then you should have no problem. Your old and new data will be merged. On the other hand, if the data already exist, then SQL will throw an error (back to BCP) saying that you have a primary key violation (because data already exists) and the data will not be uploaded. What SQLAzureMW does not do is a diff of what is in your database and what is being uploaded.

Now, all of this said, you can tell SQLAzureMW (through Advanced button) to generate DROP TABLE statements so that it does if table exist, delete it and then create a new one. Also note that there are basically two processes in SQLAzureMW. 1) Export from your source database and 2) Import to your destination database. After the export process, SQLAzureMW lets you look at the generated TSQL before you run it against the destination database. This means that you can add / modify the script. So, for example, I have an existing database and I want to upload some new tables and modify some data in others. I can add a DELETE statement that will delete some old data from a table and so that when BCP runs, it will be able to upload new data without collision.

Does this help?

Regards,
George