固定宽度文件到 sql 服务器范围的表

我有一个文本文件，其中包含固定宽度的数据。文本文件包含 ID、数据、状态。ID 只是一个 INT.状态有 2 个 CHAR 状态名。数据是在该州注册的公司的信息。

为了将它们放在 SQL 服务器表中，我首先将文本文件转储到名为 dbo.rawcompanyinfo_delimited 的 SQLServer 表中

注意：为了便于解释，我在数据列中仅显示4列。它有 500 列。

CREATE TABLE dbo.rawcompanyinfo_delimited(ID smallint NOT NULL, Data VARCHAR(MAX) NULL, State CHAR(2));
INSERT INTO dbo.rawcompanyinfo_delimited(100,'ABCINC    111  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(200,'APPLE     213  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(300,'BTEC      100  123.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(400,'S INC     123  333.0 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(500,'B INC     145  123.2 USD','PA')

我还有一个映射表，告诉我存储在map中的起始位置，长度和列名。公司LenInfo

CREATE TABLE map.CompaniesLenInfo(Startingposition int not null, Length int not null, columnnames varchar(100) not null)
insert into map.CompaniesLenInfo(1,10,CompanyName)
insert into map.CompaniesLenInfo(11,3,CompanyID)
insert into map.CompaniesLenInfo(15,5,TotalIncome)
insert into map.CompaniesLenInfo(21,3,Currency)

我写了一个嵌套光标，它循环遍历地图。CompaniesLenInfo，然后通过dbo.rawcompanyinfo_delimited并将结果存储在表中。像下面一样

CREATE TABLE dbo.output(ID INT , CompanyName VARCHAR(10),CompanyID VARCHAR(3),TotalIncome VARCHAR(5),Currency VARCHAR(3)) ;
INSERT INTO dbo.output(100,'ABCINC','111','333.5','USD','PA')
INSERT INTO dbo.output(200,'APPLE','213','333.5', 'USD','PA')
INSERT INTO dbo.output(300,'BTEC'      ,'100',  '123.5', 'USD','PA')
INSERT INTO dbo.output(400,'S INC'     ,'123',  '333.0', 'USD','PA')
INSERT INTO dbo.output(500,'B INC'     ,'145',  '123.2', 'USD','PA')

DECLARE @ID INT,@StartPosition INT,@Len INT;
DECLARE @Data NVARCHAR(MAX), @ColumnName VARCHAR(100),@Val VARCHAR(MAX),@CompanyID CHAR(9),@State_Code  VARCHAR(2);
DECLARE @Currency VARCHAR(10);
DECLARE @FinCursor AS CURSOR;
DECLARE @ParsingCursor AS CURSOR;
SET @FinCursor=CURSOR FAST_FORWARD FOR SELECT ID,Data 
FROM map.CompaniesLenInfo WHERE State='PA';
OPEN @FinCursor;
FETCH NEXT FROM @FinCursor INTO @ID,@Data;
WHILE @@FETCH_STATUS = 0 
BEGIN

  SET @ParsingCursor = CURSOR FAST_FORWARD FOR SELECT StartPosition,Length,columnnames
  FROM dbo.rawcompanyinfo_delimited;
  OPEN @ParsingCursor;
  FETCH NEXT FROM @ParsingCursor INTO @StartPosition, @Len, @ColumnName;
  WHILE @@FETCH_STATUS = 0 
      BEGIN
           SET @Val = SUBSTRING(@Data,@StartPosition, @Len);
           /* Not sure how to insert into dbo.output*/
          FETCH NEXT FROM @ParsingCursor INTO @StartPosition, @Len, @ColumnName;
      END
  CLOSE @ParsingCursor;
  DEALLOCATE @ParsingCursor;

FETCH NEXT FROM @FinCursor INTO @ID,@Data;
END
CLOSE @FinCursor;
DEALLOCATE @FinCursor;

如何作为宽表插入。任何人都可以建议任何其他可以更快的方法吗？

使用命令行工具bcp ，大容量复制程序。 bcp 将分析和加载数据。您需要定义一个"格式化文件"来定义固定宽度列的开始和停止位置。如果可以获取由另一个字符（例如 ~）分隔的数据，则更容易定义列分隔符和行终止符。

以下是文档参考：https://learn.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server

下面是使用 PIVOT 的示例：

创建表 dbo.rawcompanyinfo_delimited（ID SMALLINT NOT NULL， Data VARCHAR（MAX） NULL， STATE CHAR（2））;

INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(100,'ABCINC    111  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(200,'APPLE     213  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(300,'BTEC      100  123.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(400,'S INC     123  333.0 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(500,'B INC     145  123.2 USD','PA')
CREATE TABLE CompaniesLenInfo(Startingposition int not null, Length int not null, columnnames varchar(100) not null)
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(1,10,'CompanyName')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(11,3,'CompanyID')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(15,5,'TotalIncome')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(21,3,'Currency')
SELECT *
FROM (
    SELECT r.ID,r.State,SUBSTRING(r.Data,ci.Startingposition,ci.Length) AS val,ci.columnnames 
    FROM rawcompanyinfo_delimited AS r,CompaniesLenInfo AS ci
) AS t PIVOT(MAX(val) FOR columnnames IN (CompanyName,CompanyID,TotalIncome,Currency) ) p

+-----+-------+-------------+-----------+-------------+----------+|身份证 |状态 |公司名称 |公司编号 |总收入 |货币 |+-----+-------+-------------+-----------+-------------+----------+|100 |PA |农银 |111 | 333. | 美国 ||200 |PA |苹果 |213 | 333. | 美国 ||300 |PA |贝特科 |100 | 123. | 美国 ||400 |PA |S INC |123 | 333. | 美国 ||500 |PA |B INC |145 | 123. | 美国 |+-----+-------+-------------+-----------+-------------+----------+

无需使用游标。要将原始数据表中的数据加载到输出表中，可以使用单个（如果您有 500 列，则可能有点长）INSERT SELECT 语句：

INSERT INTO dbo.output(ID,CompanyName,CompanyID,TotalIncome,Currency)
SELECT r.ID, 
      (SELECT Substring(r.Data, m.startingposition, m.length)
         FROM map.CompaniesLenInfo AS m
        WHERE m.columnnames = 'CompanyName'),
      ...
      (SELECT Substring(r.Data, m.startingposition, m.length)
         FROM map.CompaniesLenInfo AS m
        WHERE m.columnnames = 'Currency')
  FROM dbo.rawcompanyinfo_delimited AS r

相关内容

最新更新

热门标签：