如何从与表没有相同列的excel文件批量插入？

I have an excel file with data for these columns: ID, Name, Phone Number. But the table in SQL also takes the address - which isn't in the excel file. This is causing the following error:

我有一个excel文件，其中包含以下列的数据：ID，名称，电话号码。但SQL中的表也采用了地址 - 这不在excel文件中。这导致以下错误：

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for column address

列地址的批量加载数据转换错误（类型不匹配或指定代码页的无效字符）

If I remove the address column from the table in SQL Server, I do not get this error upon import. I am ok with the address field being blank after import.

如果我从SQL Server中的表中删除地址列，导入时我不会收到此错误。我很好，导入后地址字段为空。

Here is my SQL for import:

这是我的导入SQL：

BULK INSERT tableName FROM filePath
WITH (
Datafiletype = 'char',
Fieldterminator = '\t',
Rowterminator = '\n',
Firstrow = 2
)

I am using SQL Server.

我正在使用SQL Server。

1 个解决方案

#1

You can create a view:

您可以创建一个视图：

create view v_tableName as
    select id, name, phone_number
    from tableName;

Then load the file into the view:

然后将文件加载到视图中：

BULK INSERT v_tableName FROM filePath
    WITH (Datafiletype = 'char',
          Fieldterminator = '\t',
          Rowterminator = '\n',
          Firstrow = 2
         );

#1