如何在不使用FIELDTERMINATOR的情况下通过TSQL批量插入 [英] How to Bulk Inserts via TSQL without using FIELDTERMINATOR
本文介绍了如何在不使用FIELDTERMINATOR的情况下通过TSQL批量插入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好,
我想从.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屋!
查看全文