BCP-导入具有IDENTITY列的表时,转换规范的字符值无效 [英] BCP - Invalid character value for cast specification when importing to table with IDENTITY column
问题描述
我正在尝试将文件从UNIX加载到SQL Server.当我运行BCP时,出现以下错误,但是当我将数据类型更改为Char时,我能够加载相同的文件.
I am trying to load a file from UNIX to SQL Server. When I run the BCP getting the error below, but I was able to load the same file when I changed the datatype to Char.
使用的命令:
-bcp [SQLAAA].[APP_XXX].[ACTIVITY_V1] in /home/XXX_ACTIVITY.txt -c -S xddb001 -e /home/ERRORS.log -t "|" -r "\n" -U test12 -P test12
--bcp [SQLAAA].[APP_XXX].[ACTIVITY_V1] in /home/XXX_ACTIVITY.txt -c -S xddb001 -e /home/ERRORS.log -t "|" -r "\n" -U test12 -P test12
源文件:
222|2017-12-27|Y|ABC|2017-12-27|ABC|2017-12-27
255|2018-01-04|N|ABC|2018-01-04|ABC|2018-01-04
错误消息:
@第1行,第7列:强制转换规范的字符值@
@ Row 1, Column 7: Invalid character value for cast specification @
定义表:
CREATE TABLE [APP_XX].[ACTIVITY]
(
[C1] [varchar](10) NOT NULL,
[C2_DATE] [date] NOT NULL,
[c3] [varchar](1) NULL,
[C4_NM] [varchar](10) NULL,
[C5_DTM] [date] NULL,
[C6_NM] [varchar](10) NULL,
[C7_DTM] [date] NULL,
[ROW_ID] [bigint] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
能否让我知道可以进行哪些修改?
Could you please let me know what modification can be done?
推荐答案
当导入具有IDENTITY列的表时,并且您不为导入文件中的IDENTITY列提供值时,需要在格式文件中指定在导入中将跳过IDENTITY列.
When importing into a table with an IDENTITY column, and you do not supply values for the IDENTITY column in the import file, you need to specify in a format file that the IDENTITY column will be skipped in the import.
Microsoft文档中明确提到了这种工作方式在BCP上,标记-E :
如果数据文件不包含表或视图中的Identity列的值,请使用格式文件指定在导入数据时应跳过表或视图中的Identity列; SQL Server自动为该列分配唯一值.[...]
If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view should be skipped when importing data; SQL Server automatically assigns unique values for the column.[...]
您需要做什么:
- 首先
- 使用
- First create a format file using BCP. E.g. for a non-XML format file, the command looks like
BCP table_or_view format nul -f format_file_name
- Specify to skip the IDENTITY column in the format file
- Use the format file you created in your
BCP IN
command using the BCP-f
flag
这篇关于BCP-导入具有IDENTITY列的表时,转换规范的字符值无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!