When using SqlBulkCopy, pay attention to the use of default columns in SQL Server tables

SqlBulkCopy values of the String type from the data source cannot be converted to nvarchar of the specified target column.

I found it online, mostly because the field in the database is too small (

The value of the String type from the data source cannot be converted to the nvarchar type of the specified target column.
), Resulting in a truncation error. After careful check, I found that no field is too small in my table design, and it is not a single quotation mark problem.

After careful debugging, I found that my text file does not correspond to the table columns in the database when using SqlBulkCopy for import. My text file contains 18 fields, while my table only uses 9 fields, there are two fields that are not in the text file (that is, the [Level] and [Cagegory] fields in table structure 1), which are filled with the default values by the database. The table structure is as follows:

Copy codeThe Code is as follows: table structure 11
Create table [dbo]. [Ryxx] (
[Name] [nvarchar] (30) not null,
[IdCardNo] [nvarchar] (30) not null,
[Sex] [nvarchar] (2) not null,
[Height] [nvarchar] (5) NULL,
[Level] [nvarchar] (2) NULL, -- the default value is
[Category] [nvarchar] (20) NULL, -- the default value is "Key Management"
[Partition sor] [nvarchar] (100) NULL,
[Contact] [nvarchar] (30) NULL,
[Phone] [nvarchar] (50) NULL,
[Number] [nvarchar] (30) NULL

) ON [PRIMARY]

Table Structure 1

The modified table structure is as follows:Copy codeThe Code is as follows: create table [dbo]. [Ryxx] (
[Name] [nvarchar] (30) not null,
[IdCardNo] [nvarchar] (30) not null,
[Sex] [nvarchar] (2) not null,
[Height] [nvarchar] (5) NULL,
[Partition sor] [nvarchar] (100) NULL,
[Contact] [nvarchar] (30) NULL,
[Phone] [nvarchar] (50) NULL,
[Number] [nvarchar] (30) NULL,
[Level] [nvarchar] (2) NULL, -- the default value is
[Category] [nvarchar] (20) NULL -- the default value is "Key Management"
) ON [PRIMARY]

Now, the problem is solved. The reason for the analysis is that the fields obtained from the text file correspond to the default columns in the table, resulting in truncation. Think about this error when using bcp.