Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from serverfault.com Aug 18 '12 at 13:31

This question came from our site for professional system and network administrators.

2 Answers

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.

share|improve this answer
add comment
up vote 0 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.