BCP - Invalid character value for cast specification when importing to table with IDENTITY column
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.
This way of working is explicitely mentioned in the Microsoft documentation on BCP, flag -E:
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.[...]
What you need to do:
- 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
vedamurthy
Updated on July 09, 2022Comments
-
vedamurthy almost 2 years
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.
Command used:
--
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
Source file:
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
Error Message:
@ Row 1, Column 7: Invalid character value for cast specification @
Definition table:
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?
-
vedamurthy over 6 yearsMany thanks for your solution. I have created format file and able to load Identity field. You saved me:)
-
pim about 6 yearsFor what it's worth. This exact situation can also occur if the definitions of the output and input do not match.