SQL Server Management Studio 2012 - 从/导出/导入数据 [英] SQL Server Management Studio 2012 - Export/Import data from/to table

问题描述

我有超过3 000 000行的表格。我已尝试从其中手动导出数据，并使用SQL Server Management Studio 导出数据功能到Excel，但我遇到了几个问题：

• 当创建.txt文件手动复制和粘贴数据（这是几次，因为如果从SQL Server Management Studio复制所有行，它将丢失内存错误）我无法用任何文本编辑器打开它并复制行;




• 导出数据到Excel不起作用，因为Excel不支持这么多行

最后，使用导出数据功能我创建了一个 .sql 文件，但是它是1.5 GB，我无法打开它SQL Server Management Studio再次。

有没有办法使用导入数据功能或其他更聪明的方式进行备份来导入的表格信息，然后如果需要，再次导入？

提前感谢。

解决方案

我不太清楚我是否理解你的要求（我不知道你是否需要将你的数据导出到excel或者你想做某种备份）。 >

为了从单个表导出数据，您可以使用 批量复制工具 ，它允许您从单个表导出数据并导出/导入到文件。您还可以使用自定义查询导出数据。

重要的是，这不会生成Excel文件，而是另一种格式。您可以使用它将数据从一个数据库移动到另一个数据库（在这两种情况下都必须是MS SQL）。

示例：
创建格式文件： p>

Bcp [TABLE_TO_EXPORT]格式[EXPORT_FILE]-n -f[FORMAT_FILE]-S [SERVER] -E -T -a从表中导出所有数据：

bcp [TABLE_TO_EXPORT] out[EXPORT_FILE]-f[FORMAT_FILE]-S [SERVER] -E -T -a 65535

导入以前导出的数据：

bcp [TABLE_TO_EXPORT]在[EXPORT_FILE]-f[FORMAT_FILE] - S [SERVER] -E -T -a 65535

我将输出从hte导出/导入操作重定向到日志文件（通过追加> mylogfile.log广告命令的结尾） - 这有助于您导出大量数据。

I have table with more than 3 000 000 rows. I have try to export the data from it manually and with SQL Server Management Studio Export data functionality to Excel but I have met several problems:

• when create .txt file manually copying and pasting the data (this is several times, because if you copy all rows from the SQL Server Management Studio it throws out of memory error) I am not able to open it with any text editor and to copy the rows;

• the Export data to Excel do not work, because Excel do not support so many rows

Finally, with the Export data functionality I have created a .sql file, but it is 1.5 GB, and I am not able to open it in SQL Server Management Studio again.

Is there a way to import it with the Import data functionality, or other more clever way to make a backup of the information of my table and then to import it again if I need it?

Thanks in advance.

解决方案

I am not quite sure if I understand your requirements (I don't know if you need to export your data to excel or you want to make some kind of backup).

In order to export data from single tables, you could use Bulk Copy Tool which allows you to export data from single tables and exporting/Importing it to files. You can also use a custom Query to export the data.

It is important that this does not generate a Excel file, but another format. You could use this to move data from one database to another (must be MS SQL in both cases).

Examples: Create a format file:

Bcp [TABLE_TO_EXPORT] format "[EXPORT_FILE]" -n -f "[ FORMAT_FILE]" -S [SERVER] -E -T -a 65535

Export all Data from a table:

bcp [TABLE_TO_EXPORT] out "[EXPORT_FILE]" -f "[FORMAT_FILE]" -S [SERVER] -E -T -a 65535

Import the previously exported data:

bcp [TABLE_TO_EXPORT] in [EXPORT_FILE]" -f "[FORMAT_FILE] " -S [SERVER] -E -T -a 65535

I redirect the output from hte export/import operations to a logfile (by appending "> mylogfile.log" ad the end of the commands) - this helps if you are exporting a lot of data.

这篇关于SQL Server Management Studio 2012 - 从/导出/导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！