This section contains the following examples:
- A. Copying table rows into a data file (with a trusted connection)
- B. Copying table rows into a data file (with Mixed-mode Authentication)
- C. Copying data from a file to a table
- D. Copying a specific column into a data file
- E. Copying a specific row into a data file
- F. Copying data from a query to a data file
- G. Creating a non-XML format file
- H. Creating an XML format file
- I. Using a format file to bulk import with bcp
A. Copying table rows into a data file (with a trusted connection)
B. Copying table rows into a data file (with mixed-mode authentication)
The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format.
The example assumes that you are using mixed-mode authentication, you must use the -U switch to specify your login ID. Also, unless you are connecting to the default instance of SQL Server on the local computer, use the -S switch to specify the system name and, optionally, an instance name.
bcp AdventureWorks.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
The system will prompt you for your password.
C. Copying data from a file to a table
The following example illustrates the in option by using the file created in the preceding example (Currency.dat). First, however, this example creates an empty copy of the AdventureWorks Sales.Currency table, Sales.Currency2, into which the data is copied. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
To create the empty table, in Query Editor, enter the following command:
USE AdventureWorks;
GO
SELECT * INTO AdventureWorks.Sales.Currency2
FROM AdventureWorks.Sales.Currency WHERE 1=2
To bulk copy the character data into the new table, that is to import the data, enter the following command at a command prompt:
bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c
To verify that the command succeeded, display the contents of the table in Query Editor, and enter:
USE AdventureWorks;
GO
SELECT * FROM Sales.Currency2
D. Copying a specific column into a data file
To copy a specific column, you can use the queryout option. The following example copies only the Name column of the Sales.Currency table into a data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c
E. Copying a specific row into a data file
To copy a specific row, you can use the queryout option. The following example copies only the row for the contact named Jarrod Rana from the AdventureWorks.Person.Contact table into a data file (Jarrod Rana.dat). The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp "SELECT * FROM AdventureWorks.Person.Contact WHERE FirstName='Jarrod' AND LastName='Rana' " queryout "Jarrod Rana.dat" -T -c
F. Copying data from a query to a data file
To copy the result set from a Transact-SQL statement to a data file, use the queryout option. The following example copies the names from the AdventureWorks.Person.Contact table, ordered by last name then first name, into the Contacts.txt data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T
G. Creating a non-XML format file
The following example creates a non-XML format file, Currency.fmt, for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp AdventureWorks.Sales.Currency format nul -T -c -f Currency.fmt
For more information, see Understanding Non-XML Format Files [ http://msdn2.microsoft.com/en-us/library/ms191479.aspx ] .
H. Creating an XML format file
The following example creates an XML format file named Currency.xml for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp AdventureWorks.Sales.Currency format nul -T -c -x -f Currency.xml
.gif) Note: |
| To use the -x switch, you must be using a bcp 9.0 client. For information about how to use the bcp 9.0 client, see "Remarks."
|
For more information, see Understanding XML Format Files [ http://msdn2.microsoft.com/en-us/library/ms187833.aspx ] .
I. Using a format file to bulk import with bcp
To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the in option. For example, the following command bulk copies the contents of a data file, Currency.dat, into a copy of the Sales.Currency table (Sales.Currency2) by using the previously created format file (Currency.xml). The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -f Currency.xml
| Note: |
| Format files are useful when the data file fields are different from the table columns; for example, in their number, ordering, or data types. For more information, see Format Files for Importing or Exporting Data [ http://msdn2.microsoft.com/en-us/library/ms190393.aspx ] .
|
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 25岁的心里话
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现