SQL Server excel Import and Export and FAQs

A few days ago, the examination system imported and exported student information. The first contact with the examination system imported and exported data was shocked by the simplicity of the data transmission methods of sqlserver and excel and the efficient compatibility of MS products, but also encountered various problems, this section describes the SQL Server excel Import and Export methods and problems. SQL Server has fewer Excel Export Steps and fewer problems.

A few days ago, the examination system imported and exported student information. The first contact with the examination system imported and exported data was shocked by the simplicity of the data transmission methods of sqlserver and excel and the efficient compatibility of MS products, but also encountered various problems, this section describes the SQL Server excel Import and Export methods and problems. SQL Server has fewer Excel Export Steps and fewer problems.

A few days ago, the examination system imported and exported student information. The first contact with the examination system imported and exported data was shocked by the simplicity of the data transmission methods of sqlserver and excel and the efficient compatibility of MS products, but also encountered various problems, this section describes the SQL Server excel Import and Export methods and problems.

SQLServer export Excel

There are fewer Export Steps and fewer problems.

1. Go to Excel --- data --- from other sources --- From SQLServer.

:

2. Connect to the database:

3. Select database and table --- finish:

4. Select Insert Location --- OK --- display the export result:

SQLServer import excel

1. Select the database to import --- task --- import data,

2. Select an excel Data Source-browse a file. Note: If the first row of data in excel is a field, select "First row containing column name ".

3. Select database --- Select source table and original view:

Note: If you do not select a target, a table with the same name will be automatically generated in the database and the data will be imported.

4. If you do not need to save the SSIS package, click Finish.

The import is completed only when no error occurs. The error example is as follows:

Operation stopped... -initializing Data Flow task (successful)-initializing connection (successful)-setting SQL command (successful)-setting source connection (successful)-setting target connection (successful) -Verifying (successful)-Preparing for execution (successful)-before execution (successful)-executing (successful)-copying to [dbo]. [t_Student] (error) Message error 0xc0202009: Data Flow Task 1: SSIS error code DTS_E_OLEDBERROR. An ole db error occurs. Error code: 0x80004005. Ole db records have been obtained. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 note: "unspecified error ". (SQL Server Import and Export wizard) Error 0xc020901c: Data Flow Task 1: Input "Destination Input" (60) on the Input column "studentId" (127) error. The returned column status is: "This value violates the integrity constraints of this column .". (SQL Server Import and Export wizard) Error 0xc0209029: Data Flow Task 1: SSIS error code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. "Input" Destination Input "(60)" failed. The error code is 0xC020907D, and the error row processing setting for "Input" Destination Input "(60)" fails once an error occurs. An error occurs on the specified object of the specified component. An error message may have been sent before, providing detailed information about the failure. (SQL Server Import and Export wizard) Error 0xc0047022: Data Flow Task 1: SSIS error code DTS_E_PROCESSINPUTFAILED. When processing Input "Destination Input" (60), the ProcessInput method of the component "target-t_Student" (47) fails with the error code 0xC0209029. The component identified returns an error from the ProcessInput method. Although this error is unique to this component, it is fatal and will cause the data flow task to stop running. An error message may have been sent before, providing detailed information about the failure. (SQL Server Import and Export wizard)-message information 0x402090df after execution (successful): Data Flow Task 1: "component" target-t_Student "(47) "the final submission of the data insertion Operation has started. (SQL Server Import and Export wizard) Information 0x402090e0: Data Flow Task 1: the final submission of the data insertion Operation in "component" target-t_Student "(47)" has ended. (SQL Server Import and Export wizard) Information 0x4004300b: Data Flow Task 1: "component" target-t_Student "(47)" has been written to 0 rows. (SQL Server Import and Export wizard)

In my personal experience, the second error message is the most useful and intuitive display of non-system problems, as shown in the preceding figure: "error 0xc020901c: Data Flow Task 1: an error occurred while inputting "studentId" (127) in the Input column "Destination Input" (60. The returned column status is: "This value violates the integrity constraints of this column ."

FAQs:

1. Foreign key constraints, for example, ole db records have been obtained. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 note: "The INSERT statement conflicts with the FOREIGN KEY constraint" FK_t_Student_t_Classes. This conflict occurs in the Database "CollegeSystemVBTest", table "dbo. t_Classes", column 'classid '." This problem is usually triggered when the foreign key constraint is not updated or inserted. For more information, see my blog "allow foreign keys and cascade".

2. integrity constraints of columns. For example, "error 0xc020901c: Data Flow Task 1:" studentId "(127) error in the Input column" Destination Input "(60. The returned column status is: "This value violates the integrity constraints of this column ." Generally, the primary key constraint triggers this problem. For example, the primary key in the imported excel file is duplicate. Think about it at that time because it was more than 2000 rows of student information, the conversion of students, source data errors will generate duplicate rows, the way was to first import Access, find duplicate columns and then delete, although it can solve the problem, but the efficiency is too low, a better solution, excel comes with the function of removing repeated columns, go to excel --- data --- Delete repeated items.

3. The ing column is redundant. Although SQLserver can intelligently identify ing columns, the success rate is not 100%. Therefore, you can edit the ing column or select to generate a ing column in step 3 of importing excel.

4. Data Length. This problem is also very common. The default maximum character length of an excel cell is 255. If it exceeds 255, an error occurs.

Wenwei

In general, although errors are not so easy to understand and find, you only need to observe them carefully to find out the error. Otherwise, even if a small error occurs, it will consume more than half a day. Reflection: carefully observe every prompt, targeted solutions, multi-angle attempts, and do not want to solve the problem as long as you want, but also learn the process of solving the problem.