sql-server - 数据库从 txt 文件导入 SQL Server 不起作用

Question

我正在尝试将以下数据库(在 txt 文件中)导入到我的 SQL 服务器数据库中:

Maxmind Free World Cities Database

我已经定义了一个合适的表，然后尝试使用以下查询来导入:

BULK INSERT Cities FROM 'C:\Users\***************\worldcitiespop.txt'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

但是出现如下错误:

Msg 4866, Level 16, State 8, Line 1 The bulk load failed. The column is too long in the data file for row 1, column 7. Verify that the field terminator and row terminator are specified correctly. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

似乎认为没有新行行终止符。我该怎么做才能正确导入数据库？

Answer 1

首先用这个内容创建worldcitiespop.fmt文件(文件末尾需要一个空行):

10.0
7
1  SQLCHAR  0  10 ","        1  Country                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  100 ","        2  City                SQL_Latin1_General_Cp437_BIN
3  SQLCHAR  0  100 ","        3  AccentCity                SQL_Latin1_General_Cp437_BIN
4  SQLCHAR  0  100 ","        4  Region                SQL_Latin1_General_Cp437_BIN
5  SQLCHAR  0  10 ","        5  Population                SQL_Latin1_General_Cp437_BIN
6  SQLCHAR  0  20 ","        6  Latitude                SQL_Latin1_General_Cp437_BIN
7  SQLCHAR  0  20 "\n"      7  Longitude        SQL_Latin1_General_Cp437_BIN

然后像这样加载:

INSERT INTO Cities
SELECT  a.Country, 
        a.City, 
        a.AccentCity, 
        a.Region, 
        CAST(a.[Population] as int) as [Population],
        CAST(a.Latitude as decimal(10,7)) as Latitude, 
        CAST(a.Longitude as decimal(10,7)) as Longitude
FROM OPENROWSET( 
    BULK 'D:\worldcitiespop.txt', FORMATFILE = 'D:\worldcitiespop.fmt', FIRSTROW = 2
    ) AS a;

在我的旧笔记本上，这个插页持续约 5 分钟