SQL Server——运行大型脚本文件

I have a database table on a development server that is now fully populated after I set it running with an import routine for a CSV file containing 1.4 million rows.

我在一个开发服务器上有一个数据库表，在我设置它运行一个包含140万行的CSV文件的导入例程之后，它现在已经被完全填充了。

I ran the Database Publishing Wizard on the table, and now I have a 286MB SQL script on my local machine. The problem is, I can't figure out how to run it. If I load it into SQL Server Management Studio Express I get an alert window that says "The operation could not be completed".

我在表上运行了数据库发布向导，现在本地机器上有了一个286MB的SQL脚本。问题是，我不知道怎么运行它。如果我将它加载到SQL Server Management Studio Express中，我将得到一个警告窗口，该窗口表示“操作无法完成”。

Any ideas on how I can get this SQL script to run?

关于如何让这个SQL脚本运行的想法?

5 个解决方案

#1

-5

Running something that large inside a single transaction is not a good idea. Therefore, I'd recommend breaking up the file into smaller, more manageable chunks.

在单个事务中运行大的东西不是一个好主意。因此，我建议将文件分解成更小、更易于管理的块。

Another option is to look at some of the other ways to import CSV data directly.

另一种选择是查看其他一些直接导入CSV数据的方法。

#2

160

use the sqlcmd tool to execute the file..

使用sqlcmd工具执行文件。

sqlcmd -S myServer\instanceName -i C:\myScript.sql

#3

This tool (Big SQL Script File Runner) on CodePlex will run any size script file with log and GUI.

CodePlex上的这个工具(大型SQL脚本文件运行器)可以使用日志和GUI来运行任何大小的脚本文件。

#4

Adding to Gulzar Nazim's answer: If you still get a failure, try specifying the codepage of your SQL file using option -f:

添加到Gulzar Nazim的答案:如果仍然失败，请尝试使用选项-f指定SQL文件的代码页:

sqlcmd -S myServer\instanceName -d databaseName -i C:\myScript.sql -f 65001

sqlcmd -S myServer\实例名-d databaseName -i:\myScript。sql - f 65001

I was trying to import a .dump file from SQLite (UTF-8 by default), and sqlcmd kept throwing an error after encountering the first special character. -f 65001 fixed it for me.

我试图从SQLite(默认的UTF-8)导入.dump文件，而sqlcmd在遇到第一个特殊字符后继续抛出错误。- f65001帮我修好了。

#5

Why not just use DTS to import the CSV file directly?

为什么不直接使用DTS来导入CSV文件呢?

#1

-5