Is it possible to filter data for copying?

Sep 16, 2014 at 12:01 PM
I have a database where most tables have a tenantId.

I would like to create a copy with only some tenants, so is it in possible to have a where filter on either reading data from the source or writing data to the target?

Thanks for a nice program by the way.
Coordinator
Sep 16, 2014 at 1:41 PM
Hi,

Well, yes you can filter data, but you will have to mess with the BCPArgsIn and BCPArgsOut parameters in file SQLAzureMW.exe.config. For example, if I wanted to select all customer who had a customer_id of 25, I would do something like this:

<add key="BCPArgsOut" value=""select * from {0} WHERE customer_id = 25" queryout {1} -E -n -C RAW"/>

Now, when you run the wizard, you could also have to only pick the tables with customer_id in them, because this will break tables that don't contain that column.

Another way would just to create a temp database and copy all of the tables that you want to filter to the temp db. Delete all of the unwanted data, then use SQLAzureMW to upload that table to your target database. Note that you can tell SQLAzureMW through Advanced options to only do data (don't create the table script) so you can upload several filtered datasets against the same table.

Anyway, it is possible.

Good luck and thanks!
George
Feb 7, 2015 at 8:34 PM
a add on to this question is it possible to only copy data for some tables?
Coordinator
Feb 7, 2015 at 8:43 PM
Yes. After you select the database you want to migrate, a "Choose Objects" screen will be displayed. By default, "Script all database objects" is selected. Select "Select specific database objects", expand the Tables node and select just the tables you want to migrate data from. You will also note toward the bottom of the screen, you will see an Advanced button. If you just want to copy data and not the table schema, then look for "Script Table / Data" and change that to "Data Only".

Hope this helps.
George