Database table data transfer between different servers using bcp.exe and mysqldump

Question

I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY.

Using MSSQL on two different servers I am using the following command lines to transfer table data between servers...

Export table data from DATAENTRY:

bcp.exe "SELECT * from database.dbo.table WHERE Modified > '{MM/DD/YYYY} 00:00:00.000'" queryout C:\export\dbtabledump.bcp -n -U{username} -P{password}

##FTP upload file: dbtabledump.bcp to PRODUCTION##

Import table data into PRODUCTION:

bcp.exe database.dbo.table in "C:\import\dbtabledump.bcp" -n -U{username} -P{password}

Now I would like to trim down the DATAENTRY server to the bare essentials and use LAMP instead of the current WIN/IIS/MSSQL combo. Database schema will be identical on both platforms.

Here are my following quesitons:

• What is the equivalent of MSSQL BCP.EXE in MYSQL?

Currently I have found mysqldump command

• Is the exported mysqldump file compatible with MSSQL BCP.EXE?

• If not, how do I prepare the MYSQL table data dump file for MSSQL BCP.EXE to import?

• I'm open to any other ideas, is there a more elegant solution to my workflow?

Answer 1

No you can't take the output of mysqldump and use it with bcp. Mysqldump creates a SQL Script which can then be run. If the schema is the same you can probably just run the outputted script using sqlcmd.

Answer 2

I solved it by following mrdenny's hint on using sqlcmd. Since BCP format cannot be exported from MySQL, I used CSV format instead.

The steps are to:

1. Export CSV file from MySQL
2. Use sqlcmd to import CSV file into a temporary table in MSSQL
3. In temp table, massage the data if there are any weird string problems during import
4. Reinsert data rows from temp table into the final target table
5. Drop temporary table