在 SQL 中批量插入 .txt 文件

2022-01-09 00:00:00 bulkinsert insert tsql sql-server

我正在尝试将 .txt 文件导入高级查询工具(我使用的 SQL 客户端).到目前为止，我有:

I'm trying to import a .txt file into Advanced Query Tool (the SQL client I use). So far, I have:

CREATE TABLE #tb_test
(
id INTEGER,
name varchar(10),
dob date,
city char(20),
state char(20),
zip integer
);

insert into #tb_test
values
(1,'TEST','2015-01-01','TEST','TEST',11111)
;

bulk insert #tb_test
from 'h:	bdata.txt'
    with
    (
    fieldterminator = '	',
    rowterminator = '
'
    );

我收到一条错误消息，指出第 1 行存在语法错误.我是否缺少 #tb_test 所在的数据库(如 db.#tb_test)?

I receive an error message saying there's a syntax error on line 1. Am I missing a database from which #tb_test comes (like db.#tb_test)?

这是 tbdata.txt 文件中的一行:

Here's a line from the tbdata.txt file:

2,'TEST2','2012-01-01','TEST','TEST',21111

推荐答案

我很好奇这个问题，我找到了以下解决方案:

I was curious with this question and I found the following solution:

您的数据以逗号分隔，但您尝试按 TAB 分隔两个选项:将文件数据更改为 TAB 分隔 或将 fieldterminator = ' ' 更改为 fieldterminator = ','

Your data is comma separated but you are trying to split by TAB two options: change the file data to be TAB separated or change the fieldterminator = ' ' to fieldterminator = ','

直接从文件加载时 DATE 格式存在问题，我最好的解决方案是将临时字段 dob 更改为 VARCHAR(20) 类型，然后在传递到最终显示/数据存储时转换为 DATE.

The DATE format has issues when loading directly from a file, my best solution is to change the temp field dob to type VARCHAR(20) and then, when passing to the final display/data storage convert to DATE.

以下是更正后的代码:

CREATE TABLE #tb_test
(
id INTEGER,
name varchar(10),
dob varchar(20),
city char(20),
state char(20),
zip integer
);

insert into #tb_test
values
(1,'TEST','2015-01-01','TEST','TEST',11111)
;

bulk insert #tb_test
from 'h:	bdata.txt'
    with
    (
    fieldterminator = ',',
    rowterminator = '
'
    );

相关文章