SQL 如何将管道分隔的文本文件数据导入 SQLServer 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4967006/
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 to import pipe delimited text file data to SQLServer table
提问by devnull
I have database table represented as text file in the following pattern:
我将数据库表表示为以下模式的文本文件：
0|ALGERIA|0| haggle. carefully f|
1|ARGENTINA|1|al foxes promise|
2|BRAZIL|1|y alongside of the pendal |
3|CANADA|1|eas hang ironic, silent packages. |
I need to import this data to a SQL Server 2008 database table. I have created the table with the types matching the schema.
我需要将此数据导入 SQL Server 2008 数据库表。我已经创建了与模式匹配的类型的表。
How to import this data to the table?
如何将这些数据导入到表中？
EDIT: Solved by following the answer selected. Note to anyone stumbling upon this in future: The datatype needs to be converted. Refer: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/94399ff2-616c-44d5-972d-ca8623c8014e
编辑：按照选择的答案解决。请注意将来遇到此问题的任何人：需要转换数据类型。参考：http: //social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/94399ff2-616c-44d5-972d-ca8623c8014e
采纳答案by Neil Knight
You could use the Import Datafeature by right mouse clicking the database, and then clicking Tasks then Import Data. This will give you a wizard which you can specify the delimiters etc. for your file and preview the output before you've inserted any data.
您可以Import Data通过鼠标右键单击数据库，然后单击任务，然后单击来使用该功能Import Data。这将为您提供一个向导，您可以在其中指定文件的分隔符等，并在插入任何数据之前预览输出。
回答by Kris C
If you have a large amount of data you can use bcp to bulk import from file: http://msdn.microsoft.com/en-us/library/ms162802.aspx
如果您有大量数据，可以使用 bcp 从文件批量导入：http: //msdn.microsoft.com/en-us/library/ms162802.aspx
The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables... Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.
bcp 实用程序以用户指定的格式在 Microsoft SQL Server 实例和数据文件之间批量复制数据。bcp 实用程序可用于将大量新行导入 SQL Server 表中...除非与 queryout 选项一起使用，该实用程序不需要 Transact-SQL 知识。要将数据导入表中，您必须使用为该表创建的格式文件或了解表的结构以及对其列有效的数据类型。
