一个CSV文件中包含8列（COL1，COL2，...，col8）和文件的名称中包含有必须插入到表以及日期。

A csv file contains 8 columns (col1, col2, ..., col8) and the name of the file contains the date which has to be inserted into the table as well.

如果在CSV文件中的表列和列数相等，下面的查询导入的所有记录从文件到表：

If the number of columns in the table and columns in the csv file are equal the following query imports all the records from the file to the table:

query += "BULK INSERT real_data FROM '" + path + "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";

到目前为止，我还没有找到一个解决方案修改查询，使得新的记录可以包含文件名中提取的日期。无论如何，我已经创建了一个函数抽取日期：

So far I haven't found a solution to modify the query such that the new records can contain the date extracted from the filename. Anyway I have created a function to extract the date:

DateTime eventTime = extractDate(path);

和想插入eventTime到第9列从文件导入的每个记录。

and would like to insert eventTime into 9th column for each record imported from file.

有谁知道如何修改/创建查询语句从文件导入8列，并添加日期作为第9列对每个进口备案？

Does anyone know how to modify/create query statement to import 8 columns from file and add the date as 9th column for each imported record?

感谢您！

推荐答案

您可以在任意列不添加到数据集被装入BULK INSERT命令。 （SSIS包可以做到这一点，如果你想处理的复杂性。）

You cannot add an "arbitrary column" to the data set being loaded with the BULK INSERT command. (SSIS packages can do that, if you want to deal with their complexity.)

下面的技巧是有点复杂，但我已经用它成功地几次：

The following trick is a bit complex, but I've used it succesfully several times:

确定额外的列和值的名称加载到它（例如，指明MyDate和'年01月1，1980年的'） 创建的表的基础上（ATLER表MyTable中添加约束DF_TempLoad默认的1980年1月1日为指明MyDate [检查语法，它可能会关闭的] 创建的表（临时）认为，上市仅这些列是批量插入 运行对视图的BULK INSERT;不包括在视图中的柱将被分配缺省值 删除视图 删除默认约束。 Determine the name of the extra column and the value to load into it (say, MyDate and 'Jan 1, 1980') Create a (temporary) default on the table based on that (ATLER TABLE MyTable add constraint DF_TempLoad default 'Jan 1, 1980' for MyDate [check the syntax, it may be off] Create a (temporary) view on the table, listing only those columns to be bulk inserted Run the BULK INSERT against the view; the column not included in the view will be assigned the default value Drop the view Drop the default constraint.