在SQL中批量插入.txt文件 [英] Bulk insert .txt file in SQL

问题描述

我正在尝试将.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:\tbdata.txt'
    with
    (
    fieldterminator = '\t',
    rowterminator = '\n'
    );

我收到一条错误消息，说第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 ='\t '到 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 = '\t' 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:\tbdata.txt'
    with
    (
    fieldterminator = ',',
    rowterminator = '\n'
    );

这篇关于在SQL中批量插入.txt文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！