Steps to transfer data from MYSQL to MSSQL by SQL scripts.
06 October 2010
At some certain time, we may need to transfer data from MYSQL to MSSQL.
Tools is one choice.
If the above choice is difficult or not available, here are some steps to do that using SQL scripts:
Changing in scripts:
1. Export the old database as scripts for MYSQL.
2. Change "autoincrement" as "identity(1,1)"
3. Change "int(n)" as "int"
4. In data, for escaping, ' must be used, not ` or ".
5. In data, change: \"=>",\\=>\,\'=>''
6. Cannot see UTF-8 for collation there, use some other proper choice.
Changing in SQL operation in web application:
1. `abc` becomes [abc]
2. limit a,b becomes with tabletemp as(select ?,row_number()over(order by ?)as row_number from ? where ?)select * from tabletemp where row_number>a and row_number<=a+b
3. Before "group by", all non-function value must appear in "group by".
4. In insert into. autoincrement(identity) column can be ignored, but it cannot be NULL in "insert into" in MSSQL.
5. In insert into. Any ignored column except autoincrement(identity) column must be mark as "allow nulls", even if it has a default value and the default value is valid.
6. Change the 2 option related to "text4096" in php.ini. Otherwise the php can only read 4096 for type text from mssql.
7. Not support UTF8 for column. The unserialize function in PHP will encounter serious problem. Must avoid using it and UTF8.
8. The code to modify autoincrement column in MYSQL, cannot modify identity column in MSSQL. If this modification is for ordering, add another 'order' column, and set it to id after insertion.
9. mysql_affected_rows should NOT be changed to mssql_affected_rows, but to mssql_rows_affected.