一 个文本文件qtsj.txt,里面的数据如下:
59601720060705175010
59415620060705175010
59789320060705175010
59648320060705175010
61166020060705175710
60830120060705175710
60756120060705175710
61031620060705175710
其中前6位代表卡号,中间的12位代表时间和日期,最后的两位10代表机器号,请问怎么样把他们导入到sql数据表kq中
kq表的格式是
kh char (6)
chechtime datetime
jh char (6)
59601720060705175010
59415620060705175010
59789320060705175010
59648320060705175010
61166020060705175710
60830120060705175710
60756120060705175710
61031620060705175710
其中前6位代表卡号,中间的12位代表时间和日期,最后的两位10代表机器号,请问怎么样把他们导入到sql数据表kq中
kq表的格式是
kh char (6)
chechtime datetime
jh char (6)
程序代码
create table #
(
a char(20)
)
BULK Insert #
FROM 'c:/test.txt'
WITH (
ROWTERMINATOR = '/n'
)
--select * from #
go
create table kq
(
kh char(6),
checktime datetime,
jh char(10)
)
insert into kq
select left(a,6),substring(a,7,8)+' '+substring(a,15,2)+':'+substring(a,17,2),right(a,10) from #
select * from kq
go
drop table #,kq
/*
kh checktime jh
------ ------------------------------------------------------ ----------
596017 2006-07-05 17:50:00.000 0705175010
594156 2006-07-05 17:50:00.000 0705175010
597893 2006-07-05 17:50:00.000 0705175010
596483 2006-07-05 17:50:00.000 0705175010
611660 2006-07-05 17:57:00.000 0705175710
608301 2006-07-05 17:57:00.000 0705175710
607561 2006-07-05 17:57:00.000 0705175710
610316 2006-07-05 17:57:00.000 0705175710
*/
(
a char(20)
)
BULK Insert #
FROM 'c:/test.txt'
WITH (
ROWTERMINATOR = '/n'
)
--select * from #
go
create table kq
(
kh char(6),
checktime datetime,
jh char(10)
)
insert into kq
select left(a,6),substring(a,7,8)+' '+substring(a,15,2)+':'+substring(a,17,2),right(a,10) from #
select * from kq
go
drop table #,kq
/*
kh checktime jh
------ ------------------------------------------------------ ----------
596017 2006-07-05 17:50:00.000 0705175010
594156 2006-07-05 17:50:00.000 0705175010
597893 2006-07-05 17:50:00.000 0705175010
596483 2006-07-05 17:50:00.000 0705175010
611660 2006-07-05 17:57:00.000 0705175710
608301 2006-07-05 17:57:00.000 0705175710
607561 2006-07-05 17:57:00.000 0705175710
610316 2006-07-05 17:57:00.000 0705175710
*/