1

I've abc.sql file exported from MySQL Table. This abc.sql file is 16 GB in size with around 28 million insert statements.

I'm looking for option to store it in SQL Server Database.

Couple of questions:

  1. How to open 16GB file?
  2. Is it possible to load 28 Millions Insert statements from MySQL to SQL Server?
3
0

Your table is about the 95th percentile among MySQL tables. So, it is certainly possible. However, it will take a long time to insert 28M rows one at a time.

If practical, do one of these:

LOAD DATE INFILE ... -- probably the fastest
INSERT ... VALUES (...), (...), ... -- "batch" load 1000 rows at a time; very fast

Assuming you use ENGINE=InnoDB (which is preferred), have these settings:

key_buffer_size = 20M
innodb_buffer_pool_size = 1400M

16GB file on 4GB RAM with 32-bit Operating system and 32-bit MySQL -- should be no problem. It's been more than a decade since there was a 4GB limit on files. I don't think Win-7 is too old.

mysqldump creates INSERT statements, either one row per statement, or batched. (Batched runs at least 10 times as fast when reloading.)

When using mysqldump to export for some other type of server, be sure to set various settings, else it will generate MySQL-specific syntax that will croak.

(I don't know the details of the SQL Server side.)

Does SQL Server have a way to "import" from MySQL? MySQL's Workbench can import from various sources; I don't know about exporting.

0

I have come across a similar problem once when importing a 5GB SQL file. Most editors max out at 256mb or 512mb, the only editor I know of (on windows) that can open files in the GBs without issues is emEditor (there's a trial version). My old rig with 8GB of RAM (twice as much as yours but most of it was used up to 80% the entire time) and ~20-25 GB of free space on C drive could open it in ~2minutes (keep in mind my hardware was extremely old DDR3 with gen2 or gen3 i7 processor and slow drives).

The biggest selling point (even if you have to wait for it to load) is being able to view the schemas and the code in case the export didn't include DDL for the tables (which was the case in my scenario).

Depending on the exporting tool used, your insert statements might have ` for strings instead of ', thankfully using the emEditor you can replace all of them with '. This will allow you to import your code into SQL Server without issues.

The way I solved it was by modifying the file so it can be natively imported into SQL Server and use SQL CMD to run the script file.

0

As suggested by others, I'd try and import the data directly from MySQL to SQL Server using the SSMS import wizard or SSIS.

If you can't do that and you're stuck with insert statements, I would try and break the file up into smaller pieces. I'd write something in powershelll, to export say a million lines at a time to a different file. You can experiment with different file sizes to see what works best.

Another option is rather than break the file up, you could strip out the insert syntax and convert the file into a BCP bulk load file. You could potentially do this on a another machine.

A 3rd possibility is to import the data to a SQL Db on a bigger machine that can handle it and then copy from SQL to SQL.

None of this is ideal, but if you have no alternative it might be worth experimenting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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