如何在不使用FIELDTERMINATOR的情况下通过TSQL批量插入 [英] How to Bulk Inserts via TSQL without using FIELDTERMINATOR

问题描述

您好，
我想从.dat文件批量插入数据。 
但问题是该文件不包含任何可以分隔不同值的字符。
实际上该文件包含由考勤机生成的代码，代码看起来像
 ** 31201201100915000100000043210001 ** 
 ** 31201205301806000200000043210011 ** 
 
以上2行是员工1天出勤** 4321 **，详情低于
 
 31  - 机器代码
 2012  - 年
 01  - 月
 10  - 日
 09  - 小时
 15  - 最低
 0001  - 输入或输出（0001为In& 0002 for out）
 0000000061  - 雇员代码
 0001  - 终端号码（终端号为0001，终端号码为0011）
 
我可以批量导入此档案吗？如果是的话怎么样？任何人都可以告诉我如何解决这个问题？ 
 
谢谢
 
我正在使用SQL server 2000 :( 

解决方案

您可以将计算机生成的代码插入到临时表中的列中，然后使用 SUBSTRING [ ^ ]从该列中检索数据的功能。

这是一个代码示例

  CREATE   TABLE  tblSample 
（
 Col1  VARCHAR （MAX）
）

您的批量插入内容如下所示

  BULK   INSERT  tblSample 
  FROM  ' 您的文件路径' 
  WITH  
（
 ROWTERMINATOR = '  \ n' 
）

示例代码如何使用substring函数将数据拆分为单列。

  INSERT   INTO  tblSample 
  SELECT  '  ** 31201201100915000100000043210001 **'  UNION   ALL  
  SELECT  '  ** 31201205301806000200000043210011 **' 
 
  SELECT  SUBSTRING（Col1， 3 ， 2 ） AS  MachineCode 
，SUBSTRING（Col1， 5 ， 4 ） AS  [年] 
， SUBSTRING（Col1， 9 ， 2 ） AS  [月] 
，SUBSTRING（Col1， 11 ， 2 ） AS  [Day] 
，SUBSTRING（Col1， 13 ， 2 ） AS  [Hour] 
，SUBSTRING（Col1， 15 ， 2 ） AS  [Min] 
， CASE   WHEN  SUBSTRING（Col1， 17 ， 4 ）= '  0001' 那么 < span class =code-string>' 在'  WHEN  SUBSTRING（Col1， 17 ， 4 ）= '  0002' 那么 '  Out'  ELSE  SUBSTRING（Col1， 17 ， 4 ） END   AS  [InOrOut] 
，SUBSTRING（Col1， 21 ， 10 ） AS  EmployeeCode 
， CASE   WHEN  SUBSTRING（Col1， 31 ， 4 ）= '  0001' 那么 '  Terminal In'  WHEN  SUBSTRING（Col1， 31  ， 4 ）= '  0011' < span class =code-keyword> THEN  '  Terminal Out'  ELSE  SUBSTRING（Col1， 31 ， 4 ） END   AS  [TerminalInOrOut] 
  FROM  tblSample 

Hi, 
I want to bulk insert data from .dat file.
but the problem is the file doesn't contains any char by which i could separate different values..
Actually the file contain code generated by Attendance machine, the code looks like 
**31201201100915000100000043210001**
**31201205301806000200000043210011** 

Above 2 lines are Attendance of 1 day of Employee **4321**, details are below 

31 - Machine Code
2012 - Year  
01 - Month 
10 - Day 
09 - Hour 
15 - Min 
0001 - In or Out (0001 for In & 0002 for Out) 
0000000061 - Employeecode 
0001 - Terminal No (0001 for Terminal In & 0011 for Terminal Out) 

can i bulk import this file ? if yes then how? can anyone tell how i can solve this problem?

Thanks

I'm using SQL server 2000 :(

解决方案

You can insert the machine generated code into a column in a staging table and then use the SUBSTRING[^] function to retrieve the data from that column.
Here is a code sample

CREATE TABLE tblSample
(
	Col1 VARCHAR(MAX)
)

Your bulk insert would look like this

BULK INSERT tblSample
FROM 'Your file path'
WITH
(
    ROWTERMINATOR = '\n'
)

Sample code of how to use substring function to split the data in single column.

INSERT INTO tblSample
SELECT '**31201201100915000100000043210001**' UNION ALL
SELECT '**31201205301806000200000043210011**'

SELECT SUBSTRING(Col1, 3,2) AS MachineCode
    ,SUBSTRING(Col1,5,4) AS [Year]
    ,SUBSTRING(Col1,9,2) AS [Month]
    ,SUBSTRING(Col1,11,2) AS [Day]
    ,SUBSTRING(Col1,13,2) AS [Hour]
    ,SUBSTRING(Col1,15,2) AS [Min]
    ,CASE WHEN SUBSTRING(Col1,17,4) = '0001' THEN 'In' WHEN SUBSTRING(Col1,17,4) = '0002' THEN 'Out' ELSE SUBSTRING(Col1,17,4) END AS [InOrOut]
    ,SUBSTRING(Col1,21,10) AS EmployeeCode
    ,CASE WHEN SUBSTRING(Col1,31,4) = '0001' THEN 'Terminal In' WHEN SUBSTRING(Col1,31,4) = '0011' THEN 'Terminal Out' ELSE SUBSTRING(Col1,31,4) END AS [TerminalInOrOut]
FROM tblSample

这篇关于如何在不使用FIELDTERMINATOR的情况下通过TSQL批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！