从同一 .CSV 创建表时，BULK INSERT 类型不匹配

发布于 2025-01-09 12:19:10 字数 783 浏览 0 评论 0原文

我每天通过 CSV 文件接收项目的更新信息，其中包含格式为 YYY-MM-DDThh:mm:ss 的日期/时间信息

我使用 Management Studio 任务“导入平面文件...”来创建表dbo.fullItemList 并导入初始文件的内容。它将日期/时间列识别为 datetime2(7) 类型并正确导入数据。然后，我复制此表以创建一个空白表dbo.dailyItemUpdate。

我想创建一个脚本，将 CSV 文件导入到 dbo.dailyItemUpdate，使用 MERGE 函数更新 dbo.fullItemList，然后擦除 < strong>dbo.dailyItemUpdate 为第二天做好准备。

我无法开始工作的是进口。由于该表已经存在，我正在使用以下内容

BULK INSERT dbo.dailyItemUpdate
FROM 'pathToFile\ReceivedFile.csv'
WITH
(
    DATAFILETYPE = 'char',  
    FIELDQUOTE = '"', 
    FIRSTROW = 2, 
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n', 
    TABLOCK 
)

，但我在日期/时间列上收到“类型不匹配...”错误。即使“导入平面文件”功能获取了数据类型，为什么BULK INSERT 会失败？

原文

I receive update information for items on a daily basis via a CSV file that includes date/time information in the format YYY-MM-DDThh:mm:ss

I used the Management Studio task "Import Flat File..." to create a table dbo.fullItemList and import the contents of the initial file. It identified the date/time columns as type datetime2(7) and imported the data correctly. I then copied this table to create a blank table dbo.dailyItemUpdate.

I want to create a script that imports the CSV file to dbo.dailyItemUpdate, uses a MERGE function to update dbo.fullItemList, then wipes dbo.dailyItemUpdate ready for the next day.

The bit I can't get to work is the import. As the table already exists I'm using the following

BULK INSERT dbo.dailyItemUpdate
FROM 'pathToFile\ReceivedFile.csv'
WITH
(
    DATAFILETYPE = 'char',  
    FIELDQUOTE = '"', 
    FIRSTROW = 2, 
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n', 
    TABLOCK 
)

But I get a "type mismatch..." error on the date/time columns. How come the BULK INSERT fails, even though the data type was picked up by the "Import Flat File" function?

分享到QQ

分享到微博