How to open a huge .sql file

Question

I am using sql server 2012 where I need to run the script of the insert statement. But the file size is 2.7 GB. when i am going to open the file in sql editor, it shows me the SystemOutOfMemory error. Notepad also cannot open any suggestion on how to open it.

Answer 1

SQL editor can open a file up to 500 mb without very good specifications, it seems that something is wrong if you want to paste data from one database into another, try using the import / export wizard, SSIS package or command line utility that creates the table script and then insertion is not a good approach.

Answer 2

Try opening the file in a browser, and then copy the necessary lines in the editor. It worked for me.

Answer 3

I had the same problem today with mega gigabyte sql files, so I wrote a command line utility to split them into smaller files for processing.

splitfile.exe -file "largefile.sql" -batch 5000 

http://www.esensible.com/our-products/utilities/splitfile-split-large-text-files/

There is also source code (Visual Studio 2015)

Answer 4

Firstly, you are doing something wrong. Describe how and why you received the 2.7 GB file. Most likely, there is an alternative solution to your problem.

You can execute a file of this size using the sqlcmd.exe command-line utility. MSDN sqlcmd Utility

 sqlcmd.exe -S servername -U login -P password -d databasename -i BigBigFile.sql -o out.txt 

But it will be very very very slow, since the insert will be on one line

Also, I see no reason to open such a large file in the editor. But if you want, I opened the> 3Gb file in the internal editor of the FarManager3.0 x64 file manager (my Win7 x64 config, ram 8Gb, i7-3770). And it was pretty slow too

Perhaps we should look for other data transfer options:

Bulk Import / Export MSDN

MSDB SQL Server Import and Export Wizard

MSDN BACKUP and RESET

Answer 5

I faced a similar situation when I had to edit a 1.5 GB SQL file.

EmEditor has done its job.

I tried many editors, nothing worked, everything hung or did not respond (on windows).

For once or for a short time, you can use it as a trial period. For longer / permanent use, you will have to buy premium.

Below the download link is the same.

https://www.emeditor.com/#download

Answer 6

I have a similar problem with a 6 GB .sql dump that college passed me. It’s still interesting how he got to this.

I was able to open the file through a terminal on Linux using vi / vim.

Why aren’t you trying to create a virtual machine with centos or some other flavor, transfer the file there (via ssh and using filezilla) and open it there? Install the server version without a GUI.

I bet you can open it there. Otherwise, a split would be necessary.

Answer 7

Editor 010 can process files of any size. I just used it to edit a 3GB file that Chrome, TextPad and everything else crashed.

Answer 8

I opened the 5.14G sql file in the IntelliJ IDEA editor. Download does not even take 30 seconds.

Answer 9

Large mysql dump files can be opened by gVim.

Answer 10

Why not just import the file into SQL Server and view it in Enterprise Manager? You can directly import it or use PowerShell, but the latter will probably work overnight (this is easier for small files, especially for many of them). Once it appears, you can easily do whatever you want, and the file size will not be a problem.

Answer 11

You can always use the command line utility.

Open cmd and type:

 SQLCMD -S serverName -d databaseName -i yourfile.sql -U username -P password 

More information here: https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-run-transact-sql-script-files?view=sql-server-2017

I just successfully launched a 7 GB file with 8 million inserts.

Answer 12

For cross-platform applications, I think VSCode is actually the best here. If he notices that this is a large file, he will automatically turn off syntax search and all the nice features, but will leave, at least with basic search and replace, etc. I use this for 1-3 GB files regularly for Wordpress, because sometimes permalinks are not updated when changing domain names for developer sites, etc. etc.

If you have Linux or Git Bash installed on Windows, then you will have access to vim, which I recommend for just 4GB +. I had to change the rows manually in the MySQL 6GB + file and it works fine. You will think that this does not work, because reading from a disk in RAM may take about a minute, but it will work. You will want to estimate how many times you will save, because it will write as much as 6 GB back to disk, and navigation will also require the use of search, line numbers, etc., but it works.

Answer 13

I have the same problem, but I solved it.

I had a 5 GB .sql file. In this file I had to edit due to a syntax error. I tried with several editors, but that didn't work.

Then, after I tried with the Visual Studio Code Editor . And it works.

• So first download VSC from https://code.visualstudio.com/download .
• After installing VSC, you should upload your file. If the file size is larger than the original download file size.
• Then after VSC will suggest to increase the maximum file size in MB.
• Therefore, you should make the most of it as you want. (I suggest you. Increase the file size - three times as much as you want).
• Then you must restart VSC. After that you can open your file.
• And I hope the file will be open.