Sample analysis of columns that use default values in the Sqlserver table when using SqlBulkCopy

This article to share with you is about the use of SqlBulkCopy to pay attention to the use of default values in the Sqlserver table column example analysis, Xiaobian feel quite practical, so share with you to learn, I hope you can read this article after harvest, not much to say, follow Xiaobian to see it.

SqlBulkCopy A given value of type String from a data source cannot be converted to type nvarchar for the specified destination column.

Looking around the web, most say it's because the fields in the database are too small (a given value of type String from the data source cannot be converted to type nvarchar for the specified target column). ), causing truncation errors, after careful inspection found that my table design is not too small in the case of fields, nor is it a single quotation mark problem. After careful debugging, I found that my text file does not correspond to the table columns in the library when importing using SqlBulkCopy. My text file has 18 fields, but my table uses only 9 fields, and two of them are missing from the text file (i.e.,[Level] and [Cageory] fields in table structure 1) and are filled in by the database by default.

The table is structured as follows:

The 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, --Default is A [Category] [nvarchar](20) NULL, --Default is Key Management [Sponsor] [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

The code is: CREATE TABLE [dbo]. [Ryxx]( [Name] [nvarchar](30) NOT NULL, [IdCardNo] [nvarchar](30) NOT NULL, [Sex] [nvarchar](2) NOT NULL, [Height] [nvarchar](5) NULL, [Sponsor] [nvarchar](100) NULL, [Contact] [nvarchar](30) NULL, [Phone] [nvarchar](50) NULL, [Number] [nvarchar](30) NULL, [Level] [nvarchar](2) NULL, --Default is A [Category] [nvarchar](20) NULL --Default is "Key Management" ) ON [PRIMARY]

The reason for 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.

The above is an example analysis of columns using default values in the Sqlserver table when using SqlBulkCopy. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.