如何导入大型 MS SQL .sql 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/431913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you import a large MS SQL .sql file?
提问by Hyman
I use RedGate SQL data compare and generated a .sql file, so I could run it on my local machine. But the problem is that the file is over 300mb, which means I can't do copy and paste because the clipboard won't be able to handle it, and when I try to open the file in SQL Server Management Studio I get an error about the file being too large.
我使用 RedGate SQL 数据比较并生成了一个 .sql 文件，所以我可以在我的本地机器上运行它。但问题是文件超过 300mb，这意味着我无法进行复制和粘贴，因为剪贴板将无法处理它，并且当我尝试在 SQL Server Management Studio 中打开文件时出现错误关于文件太大。
Is there a way to run a large .sql file? The file basically contains data for two new tables.
有没有办法运行一个大的 .sql 文件？该文件基本上包含两个新表的数据。
回答by Ray Booysen
From the command prompt, start up sqlcmd:
从命令提示符启动sqlcmd：
sqlcmd -S <server> -i C:\<your file here>.sql
Just replace <server>with the location of your SQL box and <your file here>with the name of your script. Don't forget, if you're using a SQL instance the syntax is:
只需替换<server>为 SQL 框的位置和<your file here>脚本名称即可。不要忘记，如果您使用的是 SQL 实例，则语法是：
sqlcmd -S <server>\instance.
Here is the list of all arguments you can pass sqlcmd:
这是您可以传递 sqlcmd 的所有参数的列表：
Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f | i:[,o:]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, environment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
回答by Takuro
I had exactly the same issue and had been struggling for a while then finally found the solution which is to set -aparameter to the sqlcmdin order to change its default packet size:
我遇到了完全相同的问题并且已经挣扎了一段时间，然后终于找到了将-a参数设置sqlcmd为 以更改其默认数据包大小的解决方案：
sqlcmd -S [servername] -d [databasename] -i [scriptfilename] -a 32767
回答by Yigit Yuksel
回答by Syam Kumar
- Take command prompt with administrator privilege
- Change directory to where the .sql file stored
Execute the following command
sqlcmd -S 'your server name' -U 'user name of server' -P 'password of server' -d 'db name'-i script.sql
- 以管理员权限获取命令提示符
- 将目录更改为 .sql 文件的存储位置
执行以下命令
sqlcmd -S 'your server name' -U 'user name of server' -P 'password of server' -d 'db name'-i script.sql
回答by Animus Miles-Militis
I am using MSSQL Express 2014 and none of the solutions worked for me. They all just crashed SQL. As I only needed to run a one off scriptwith many simple insert statements I got around it by writing a little console app as a very last resort:
我正在使用 MSSQL Express 2014，但没有一个解决方案适合我。他们都只是崩溃了 SQL。因为我只需要运行一个带有许多简单插入语句的一次性脚本，我通过编写一个小控制台应用程序作为最后的手段来解决它：
class Program
{
static void Main(string[] args)
{
RunScript();
}
private static void RunScript()
{
My_DataEntities db = new My_DataEntities();
string line;
System.IO.StreamReader file =
new System.IO.StreamReader("c:\ukpostcodesmssql.sql");
while ((line = file.ReadLine()) != null)
{
db.Database.ExecuteSqlCommand(line);
}
file.Close();
}
}
回答by BobbyShaftoe
Run it at the command line with osql, see here:
使用 osql 在命令行中运行它，请参见此处：
http://metrix.fcny.org/wiki/display/dev/How+to+execute+a+.SQL+script+using+OSQL
http://metrix.fcny.org/wiki/display/dev/How+to+execute+a+.SQL+script+using+OSQL
回答by Bronek
I had similar problem. My file with sql script was over 150MB of size (with almost 900k of very simple INSERTs). I used solution advised by Takuro (as the answer in this question) but I still got error with message saying that there was not enough memory ("There is insufficient system memory in resource pool 'internal' to run this query").
我有类似的问题。我的带有 sql 脚本的文件大小超过 150MB（几乎有 900k 的非常简单的INSERT）。我使用了 Takuro 建议的解决方案（作为这个问题的答案），但我仍然收到错误消息，提示内存不足（“资源池‘内部’中的系统内存不足，无法运行此查询”）。
What helped me was that I put GOcommand after every 50k INSERTs.
帮助我的是我在每 50k INSERT之后放置GO命令。
(It's not directly addressing the question (file size) but I believe it resolves problem that is indirectly connected with large size of sql script itself. In my case many insert commands)
（它不是直接解决问题（文件大小），但我相信它解决了与大型 sql 脚本本身间接相关的问题。在我的情况下，许多插入命令）
回答by Philippe Grondier
Your question is quite similar to this one
你的问题和这个很相似
You can save your file/script as .txt or .sql and run it from Sql Server Management Studio (I think the menu is Open/Query, then just run the query in the SSMS interface). You migh have to update the first line, indicating the database to be created or selected on your local machine.
您可以将文件/脚本保存为 .txt 或 .sql 并从 Sql Server Management Studio 运行它（我认为菜单是打开/查询，然后只需在 SSMS 界面中运行查询）。您可能必须更新第一行，指示要在本地计算机上创建或选择的数据库。
If you have to do this data transfer very often, you could then go for replication. Depending on your needs, snapshot replication could be ok. If you have to synch the data between your two servers, you could go for a more complex model such as merge replication.
如果您必须经常进行此数据传输，则可以进行复制。根据您的需要，快照复制可能没问题。如果您必须在两台服务器之间同步数据，则可以采用更复杂的模型，例如合并复制。
EDIT: I didn't notice that you had problems with SSMS linked to file size. Then you can go for command-line, as proposed by others, snapshot replication (publish on your main server, subscribe on your local one, replicate, then unsubscribe) or even backup/restore
编辑：我没有注意到您在与文件大小相关的 SSMS 方面存在问题。然后，您可以按照其他人的建议使用命令行，快照复制（在主服务器上发布，在本地服务器上订阅，复制，然后取消订阅）甚至备份/恢复
回答by P Daddy
The file basically contain data for two new tables.
该文件基本上包含两个新表的数据。
Then you may find it simpler to just DTS (or SSIS, if this is SQL Server 2005+) the data over, if the two servers are on the same network.
然后，如果两台服务器在同一网络上，您可能会发现仅 DTS（或 SSIS，如果这是 SQL Server 2005+）数据更简单。
If the two servers are not on the same network, you can backup the source database and restore it to a new database on the destination server. Then you can use DTS/SSIS, or even a simple INSERT INTO SELECT, to transfer the two tables to the destination database.
如果两台服务器不在同一网络上，您可以备份源数据库并将其恢复到目标服务器上的新数据库。然后您可以使用 DTS/SSIS，甚至一个简单的INSERT INTO SELECT. 将两个表传输到目标数据库。
回答by ortegatorres 10
Hope this help you!
希望这对你有帮助！
sqlcmd -u UserName -s <ServerName\InstanceName> -i U:\<Path>\script.sql
