How do I execute more than 100 megabytes (100MB) of SQL script?

Original: http://www.cnblogs.com/hai-ping/p/3939150.html

Recently encountered a problem, in SQL Server Query Analyzer executes a database script more than 100MB, found that the "trigger type is" System.OutOfMemoryException "exception", the Internet to check a bit, The main reason is that the. SQL script files are too large (typically more than 100M) to cause memory to be unable to process so much data.

There are a variety of solutions, such as:

1, open the script file with Notepad, cut the file into 10-15m and then the text file, and then execute it;

2, or when the script is exported, the Sub-table export, so that the exported text size is not very large;

Although the above problems are simple, but a lot of steps, if the table and data too much, it is a kind of labor torture! In addition, if there is a primary foreign key relationship between the table, sub-data to be cautious, or error to make you mad!

Good! Having said so much, what is the solution?

The solution is to use cmd under the osql command, the script is not a problem, the key is that you have to be patient, the execution of large scripts, it takes a certain amount of time! Actually, too! What is Osql?

osql is a Microsoft Windows 32 command Prompt tool that you can use to run Transact-SQL statements and script files! How do I use Osql?

Type cmd in Start-to-run, using "OSQL-?" command, you can display Help for the osql command line.
Note: The options list for the osql tool is case-sensitive and is noted when used.

Note: In the we can see that osql does not support all the features of SQL Server 2012, and if you need to use all the features of SQL Server 2012, you can use the Ocmd command.

1. Execute a single Transact-SQL statement using osql

In the type cmd command similar to this one:

OSQL-E-q "Transact-SQL statement"

Example: SQL-E-Q "Use MyDB select * from MyTable"

Where-e means Microsoft Windows NT authentication is used.

You can also use the SA account to see the above command!

And-Q indicates that the Transact-SQL statement is run, but does not exit osql at the end of the query.

To run the Transact-SQL statement and exit osql, use the-q parameter instead of-Q.

1. Execute a large script file using osql

Point the tool to a script file, step:

A. Create a script file (such as Myfile.sql) that contains a batch of Transact-SQL statements.

B. Open a command prompt, type a command similar to the following, and then press ENTER:

Osql-e-I. Input_file

Where Input_file is the script file and its full path. For example, if the script file Myfile.sql in the C:\users folder,

Please replace the parameter myfile with C:\users\myfile.sql.

The result of running the script file appears in the console window.

If you want to direct the run results to a file, add the-o output_file parameter to the above command. For example:

OSQL-E-I input_file-o output_file

Where output_file is the output file and its full path.

How do I execute more than 100 megabytes (100MB) of SQL script?