SQL BULK INSERT FROM errors [英] SQL BULK INSERT FROM errors

问题描述

我正尝试将CS​​V文件插入到Microsoft SQL Server Management Studio数据库中，如下所示：

I'm attempting to insert a CSV file into an Microsoft SQL Server Management Studio database like this:

    BULK INSERT [dbo].[STUDY]
        FROM 'C:\Documents and Settings\Adam\My Documents\SQL Server Management Studio\Projects\StudyTable.csv' 
    WITH 
    ( 
        MAXERRORS = 0,
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
    )

但是我遇到了错误：

    Msg 4863, Level 16, State 1, Line 2
    Bulk load data conversion error (truncation) for row 1, column 9 (STATUS).

    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 2
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

很遗憾，我不能提供 StudyTable.csv ，以保护我们所服务的公司的隐私。

Unfortunately, I cannot provide the contents of StudyTable.csv, to protect the privacy of the company we're working for.

编辑我可以保证 csv 文件。它是从OpenOffice的Excel版本导出的，我仔细检查并确保它有效。

EDIT I can vouch for the validity of the csv file though. It was exported from OpenOffice's version of Excel, and I went through and made sure it was valid.

EDIT2
这是一个虚拟对象CSV文件的版本：

EDIT2 Here's a dummy version of the CSV file:

    1234,,,1234,1234,,"asdf","asdf","Z","asd",7/1/2010 12:23,8/5/2010 13:36,9/4/2010 13:36,"(asdf,1661,#1234,F,T)","F",,,"F",,"68866",1234,1234,1234,"F"

这是 STUDY 表的创建脚本：

CREATE TABLE [dbo].[STUDY]
(
    [STUDY_ID] INT IDENTITY(1,1) NOT NULL,
    [PARENT_ID] INT,
    [GROUP_ID] INT,
    [WORKFLOW_NODE_ID] INT,
    [STUDY_TEMPLATE_ID] INT,
    [INSPECTION_PLAN_ID] INT,
    [NAME] VARCHAR(255),
    [DESCRIPTION] VARCHAR(4000),
    [STATUS] VARCHAR,
    [OLD_STATUS] VARCHAR,
    [CREATED_ON] DATE,
    [COMPLETED_ON] DATE,
    [AUTHORIZED_ON] DATE,
    [EVENTS] VARCHAR,
    [NEEDS_REVIEW] CHAR,
    [HAS_NOTES] CHAR,
    [HAS_AUDITS] CHAR,
    [STUDY_PART] CHAR,
    [STUDY_TYPE] VARCHAR,
    [EXTERNAL_REFERENCE] VARCHAR,
    [CREATED_BY] INT,
    [COMPLETED_BY] INT,
    [AUTHORISED_BY] INT,
    [ARCHIVED_CHILD_COMPLETE] CHAR
)

推荐答案

听起来像.csv文件中 STATUS 列中的数据长于

It sounds like the data in your STATUS column in the .csv file is longer than the definition for the field in your SQL Table.

检查该字段上的定义，并确保.csv中的数据适合（长度和类型）。

Check the definition on that field and make sure the data you have in the .csv will fit (both length and type).

这篇关于SQL BULK INSERT FROM errors的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！