SQL Server 2005 - 以编程方式导出表（运行 .sql 文件以重建它）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20363/
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
SQL Server 2005 - Export table programmatically (run a .sql file to rebuild it)
提问by The Matt
I have a database with a table Customers that have some data
I have another database in the office that everything is the same, but my table Customers is empty
How can I create a sql file in SQL Server 2005 (T-SQL) that takes everything on the table Customers from the first database, creates a, let's say, buildcustomers.sql, I zip that file, copy it across the network, execute it in my SQL Server and voila! my table Customers is full
How can I do the same for a whole database?
我有一个带有表客户的数据库，其中有一些数据
我在办公室有另一个数据库，一切都相同，但我的表客户是空的
如何在 SQL Server 2005 (T-SQL) 中创建一个包含所有内容的 sql 文件在来自第一个数据库的表客户上，创建一个，比方说，buildcustomers.sql，我压缩该文件，通过网络复制它，在我的 SQL Server 中执行它，瞧！我的表客户已满
如何对整个数据库执行相同操作？
回答by The Matt
This functionality is already built in to Sql Server Management Studio 2008.
此功能已内置于 Sql Server Management Studio 2008 中。
Just download the trialand only install the client tools (which shouldn't expire). Use Management Studio 2008 to connect to your 2005 database (its backwards compatible).
只需下载试用版并仅安装客户端工具（不应过期）。使用 Management Studio 2008 连接到您的 2005 数据库（向后兼容）。
- Right click your database
- Choose Tasks> Generate Scripts
- Press Next, select your database again
- On the 'Choose Script Options' screen, there is an option called Script Datawhich will generate SQL insert statements for all your data.
- 右键单击您的数据库
- 选择任务>生成脚本
- 按下一步，再次选择您的数据库
- 在“选择脚本选项”屏幕上，有一个名为“脚本数据”的选项，它将为您的所有数据生成 SQL 插入语句。
(Note: for SQL Server Management Studio 2008 R2, the option is called "Types of data to script" and is the last one in the General section. The choices are "data only", "schema and data", and "schema only")
（注意：对于 SQL Server Management Studio 2008 R2，该选项称为“要编写脚本的数据类型”，是“常规”部分的最后一个。选项为“仅数据”、“模式和数据”和“仅模式” ”）
回答by Unsliced
Use bcp(from the command line) to a networked file and then restore it.
使用bcp（从命令行）到网络文件，然后恢复它。
e.g.
例如
bcp "SELECT * FROM CustomerTable" queryout "c:\temp\CustomerTable.bcp"
-N -S SOURCESERVERNAME -T
bcp TargetDatabaseTable in "c:\temp\CustomerTable.bcp" -N -S TARGETSERVERNAME -T
- -N use native types
- -T use the trusted connection
- -S ServerName
- -N 使用本机类型
- -T 使用可信连接
- -S 服务器名
Very quick and easy to embed within code. (I've built a database backup(restore) system around this very command.
非常快速且易于嵌入代码中。（我已经围绕这个命令构建了一个数据库备份（恢复）系统。
回答by Milica Medic
You can check the following article to see how you can do this by using both SQL Server native tools and the third party tools: SQL Server bulk copy and bulk import and export techniques
您可以查看以下文章，了解如何使用 SQL Server 本机工具和第三方工具执行此操作：SQL Server 大容量复制和大容量导入和导出技术
Disclaimer: I work for ApexSQL as a Support Engineer
免责声明：我作为支持工程师为 ApexSQL 工作
Hope this helps
希望这可以帮助
回答by Bryan Roth
You could always export the data from the Customers table to an Excel file and import that data into your Customers table.
您始终可以将客户表中的数据导出到 Excel 文件，然后将该数据导入到客户表中。
To import/export data:
导入/导出数据：
- Right click on database
- Go to Tasks
- Go to Import Dataor Export Data
- Change the data source to Microsoft Excel
- Follow the wizard
- 右键单击数据库
- 转到任务
- 转到导入数据或导出数据
- 将数据源更改为Microsoft Excel
- 跟随向导
回答by Nordin
If both databases resides in the same instance of SQL Server, ie use same connection, this SQL might be helpful:
如果两个数据库驻留在 SQL Server 的同一个实例中，即使用相同的连接，则此 SQL 可能会有所帮助：
INSERT INTO [DestinationDB].[schema].[table] ([column])
SELECT [column] FROM [OriginDB].[schema].[table]
GO
回答by Able Alias
For Data Expoer as SQL script in SQL server 2005,
对于 SQL Server 2005 中作为 SQL 脚本的 Data Expoer，
回答by Lars Ladegaard
I just like to add some screen shoots for Sql Server Management Studio 2008. It is correct to use the steps describe previously. When you have the 'Generate and Publish Script' -> 'Set Script Options' then press Advance to see script options:
我只是想为 Sql Server Management Studio 2008 添加一些屏幕截图。使用前面描述的步骤是正确的。当您拥有“生成和发布脚本”->“设置脚本选项”后，按“高级”查看脚本选项：
![Where to find Advanced script options]: image missing because I do not have the right reputation :(
！[在哪里可以找到高级脚本选项]：图像丢失，因为我没有正确的声誉:(
For Sql Server Management Studio 2008 the option to included data is 'Types of data to script'
对于 Sql Server Management Studio 2008，包含数据的选项是'Types of data to script'
![Types of data to script]: image missing because I do not have the right reputation :(
！[脚本的数据类型]：图像丢失，因为我没有正确的声誉:(
