If you have billions of records in the sql server table and you want to import these records into mysql table then you can follow the process I have described below.
Remember: Before transferring the data to mysql db you should have the table structure in mysql.
Step 1(Management Studio Setting to generate CSV file):
- Open Micrisoft Sql Server Management Studio(Sql Server 2008 R2).
- Click on Tools -> Options.
- Expand Query Results -> Expand SQL Server in Options window.
- Select Results to Text.
- Choose Tab delimited option from drop down as Output format.
See the figure below.
Step 2(Generating CSV File or Text File):
- Click on New Query in Management Studio.
- Write the query to retrieve all data from particular table.
- Execute the query.
See the figure below.
- Right click on results -> Choose Save Results As <filename>.txt(prefered).
- Open your text file and go to the bottom of the page and remove unnecessary lines if there(like 4000 records...).
- Put the file in your root directory("C:\xampp\htdocs" in windows) or as per your project root directory(Ex: /var/www in ubuntu).
See the figure below.
Last Step( Run The PHP Script ):
Here is my script. Put this file in your project directory and browse it.
****************************************************
<?php
$current_database = "MYSQL";
if($current_database == "MYSQL") {
$dbname = "nsmcollection"; //database name
$dbhost = "localhost"; // Your server name
$dbuser = "tanmaya"; //database user name
$dbpass = "mindfire"; //database password name
#Best for PHP 5.4 or above ===============================================
/* $con = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$table_name = "makers"; //Here you can change the table name
$my_file = $_SERVER['DOCUMENT_ROOT']."/data_MAKERS2.txt"; //Change the name of text file according to your text file name.
$sql_truncate = "TRUNCATE TABLE ".$table_name ;
mysqli_query($con,$sql_truncate);
$sql_insert = "LOAD DATA INFILE '".$my_file."' INTO TABLE ".$table_name."
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES";
mysqli_query($con,$sql_insert);
echo "You have successfully inserted all the data into ".$table_name." table";
*/
#Best for below PHP 5.4 version =============================================
mysql_connect($dbhost, $dbuser,$dbpass) OR DIE ('Could not connect to '.$dbhost.' server<br>' . mysql_error());
mysql_select_db($dbname) or die('Could not select the '.$dbname.' database');
$table_name = "makers"; //Here you can change the table name
$my_file = $_SERVER['DOCUMENT_ROOT']."/data_MAKERS2.txt"; //Change the name of text file according to the table
$sql_truncate = "TRUNCATE TABLE ".$table_name ;
mysql_query($sql_truncate);
$sql_insert = "LOAD DATA INFILE '".$my_file."' INTO TABLE ".$table_name."
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES";
mysql_query($sql_insert);
echo "You have successfully inserted all the data into ".$table_name." table";
}
?>
******************************************************************************
Now you can check your mysql db. Enjoy!
