Sql-server – Database table data transfer between different servers using bcp.exe and thesqldump

bcpMySQLmysqldumpsql serversqlcmd

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?

Best Answer

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.

Related Question