用触发器删除txt中指定条件内容行数据
一: 删除txt中指定条件内容行数据
下面图中TXT的文档内容是原始数据,现在我需要删除A0126开头的行内容,保留delete,insert,update开头的代码行数据.
因为从服务器上拷出来的日志代码有自动换行的,就是可能一条insert语句在txt中会有好几行.
比如图中的第5行delete代码涉及到下一行(就是5,6行都是delete的代码.)
所以针对这样的情况,第6行数据还是要保存的.
像A0126开头的代码,如图中(2,3,4行)都是要剔除的.
一开始没想到用什么方法好,只有手动删除,删的我眼冒泪光啊.而且不小心还可能删错了!
晚上下班回去后想了想,终于想出个雏形了.今天上班试了下,感觉还可以,就是过程有点繁杂.
但相对一行一行的手动删除（特别是针对上万行的数据量）,这个效果还是很明显嘀.
【思路】先把txt数据复制到excel中，然后导入到sql server 2008中（我试过txt直接导入，但是会报错。
所以我就把它复制到excel中再导入了。）
导入成功后，写个游标（下面给出具体内容）获取要剔除的行数，然后删除要剔除的行数。
最后select出结果集。
【具体步骤】：
1： 把txt内容复制到excel 2007中。 excel的设计如下图（特别要注意的是，第一行最好加个字段名称。）
A列的ID自己加的，为了标识在txt中的行数。为后面做准备。
2： 把EXCEL数据导入到Sql Server 2008 中（具体方法在此省略，百度之或者参考：http://www.cnblogs.com/ycusking/archive/2011/11/28/excel_sqlserver.html ）
成功导入数据库：（导入数据库新建的表名为'out5') 下图为'sp_help out5'的信息:
3： 最关键的一步:写游标,把要删除的内容的行数(也就是表中的id号)插入到'out_id5'表中,最后只要select出out5
中的id不在out_id5表中的数据即可:
' select note from out5 where id not in (select id from out_id5) order by id '
1 --定义游标
2 declare cursor_del5 cursor for --游标名称为:cursor_del5
3 select id,substring(note,1,5) from out5 order by id
4
5
6 --打开游标
7 open cursor_del5
8
9 declare @id int
10 declare @str char(5)
11
12
13 --读取游标中的第一条数据
14 fetch next from cursor_del5 into @id,@str
15
16 --判断是否从游标中读取数据
17 while (@@FETCH_STATUS = 0)
18 begin
19 if @str = 'A0126' --需要删除的是以'A0126'开头的一行或多行数据
20 begin
21 if not exists (select 1 from out_id5 where id = @id )
22 begin
23 ---插入第一笔'A0126开头的id
24 insert into out_id5(id,str5) select @id,@str
25 end
26
27 declare @mid int --out5中最大的id号
28 select @mid = max(id) from out5
29
30
31 --获取下一行的开头字母,如果下一行为空,则自动填字符串'kong'
32 select @id = @id + 1
33 select @str = isnull(substring(note,1,5),'kong') from out5 where id = @id
34
35
36 --如果下一行不是'delet','inser'或'updat'开头的,也要删除此行的数据(即把此行id插入out_id5表中)
37 while @str not in ('delet','inser','updat')
38 begin
39 if not exists (select 1 from out_id5 where id = @id )
40 begin
41 insert into out_id5(id,str5) select @id,@str
42 end
43
44 --如果@id小于最大id值,继续查找下一行的数据
45 if @id <= @mid
46 begin
47 select @id = @id + 1
48 select @str = isnull(substring(note,1,5),'kong') from out5 where id = @id
49 end
50 else
51 select @id = @mid +1 , @str = 'delet' --为了退出while语句
52 end
53 end
54
55
56 fetch next from cursor_del5 into @id, @str
57
58
59 end
60
61 --关闭游标
62 close cursor_del5
63
64 --删除游标
65 deallocate cursor_del5
66
67
68
执行这游标之前,需要先建个表:
create table out_id5(
id int,
str5 char(5) ---为了便于对比out5表的数据
)
建完表后,再在SSMS上执行上图中的游标代码就可以了!
成功执行完后,再执行下面的sql语句:
select id,note into out_result5 from out5 where id not in (select id from out_id5) order by id
然后select出out_result5表中的note字段,复制到txt中,就是剔除后的结果集:
这个结果集看起来还是基本符合要求的!
但是这结果集还不是最终想要的,凡是涉及到对repair_histoty,kuandai_pretreat_history这两张表进行
update,insert,delete等操作的,通通地删除,一个不留!那怎么办呢? 其实还可以继续用游标:
二: 删除指定表的操作的内容行:
在执行游标之前,还是要准备一张表del_id5 ,这张表存放的是刚刚获取的结果表out_result5中对repair_history,kuandai_pretreat_history两张表操作的的数据行数:
create table del_id5(
id int,
str5 char(5)
)
下面是游标的代码:
1 --定义游标
2 declare cursor_del1 cursor for
3 select id,substring(ltrim(substring(note,1,100)),1,5) from out_result5 order by id
4
5
6 --打开游标
7 open cursor_del1
8
9 declare @id int
10 declare @str char(5)
11
12 --读取游标中的第一条数据
13 fetch next from cursor_del1 into @id,@str
14
15 --判断是否从游标中读取数据
16 while (@@FETCH_STATUS = 0)
17 begin
18 if @str in('inser','delet','updat')
19 begin
20 declare @dnote varchar(100)
21 select @dnote = ltrim(substring(note,1,100)) from out_result5 where id = @id
22
23 --txt中,操作的表名都有一个规律,就是在upd,ins或del后面且表名前都有'dbo.'字符串
24 --我们只要根据'dbo.'来截取后面的表名,就可以判断是否要剔除此行数据了
25 if ( substring(@dnote,charindex('dbo.',@dnote)+4,14) = 'repair_history' or
26 substring(@dnote,charindex('dbo.',@dnote)+4,24) = 'kuandai_pretreat_history')
27 begin
28 if not exists (select 1 from del_id5 where id = @id )
29 begin
30 insert into del_id5(id,str5) select @id,@str
31 end
32
33
34 declare @mid int
35 select @mid = max(id) from out_result5
36 select @id = MIN(id) from out_result5 where id > @id --获取下一行id
37
38 if @id <= @mid
39 begin
40 select @str =substring(ltrim(substring(note,1,100)),1,5) from out_result5 where id = @id --获取下一行的代码内容
41
42 --如果不是'delet','inser'或'updat'开头,则还要继续剔除此行数据
43 while @str not in ('delet','inser','updat')
44 begin
45
46 if not exists (select 1 from del_id5 where id = @id )
47 begin
48 insert into del_id5(id,str5) select @id,@str
49 end
50
51 select @id = MIN(id) from out_result5 where id > @id --获取下下行id
52
53 if @id <= @mid
54 begin
55 --获取下下行代码开头的字符串,返回给while继续进行判断
56 select @str = substring(ltrim(substring(note,1,100)),1,5) from out_result5 where id = @id
57 end
58 else begin
59 select @id = @mid +1 , @str = 'delet' --标识退出while
60 end
61 end --end of while @srt ...
62 end --end of if @id <= @mid
63 end
64 end --end of if @str in('inser','delet','updat')
65
66
67 fetch next from cursor_del1 into @id, @str
68
69
70 end
71
72
73
74 --关闭游标
75 close cursor_del1
76
77 --删除游标
78 deallocate cursor_del1
79
80
81
成功执行完游标后,下面就可以获取结果集了:
select note from out_result5 where id not in (select id from del_id5) order by id
copy到txt中,效果如下图:
P.S: 如果txt文档的第一行数据不是以'update','insert','delete'开头,也不是以'A0126'开头的,那么最后的
结果集中还是会保留这一行的数据的.所以如果有此情况的话,得手动删除前面几行数据!
最后,这样看起来好像还可以,获取到了最终想要的结果集体.
但是,这还不是最佳效果,因为换行的缘故(并不是txt中自动换行),导致其中的一句insert,update或delete语句
被拆分成了2行甚至更多的行数.
那有什么办法能够合并几行的数据呢? 前面两个习惯了用游标,所以我首先想的是能否继续用游标来拼接内容呢?
三: 拼接txt中几行条件数据到一行中.
1 --定义游标
2 declare cursor_del1 cursor for
3 select id,substring(ltrim(substring(note,1,100)),1,5) from out_result10 order by id
4
5
6 --打开游标
7 open cursor_del1
8
9 declare @id int
10 declare @str char(5)
11
12 --读取游标中的第一条数据
13 fetch next from cursor_del1 into @id,@str
14
15 --判断是否从游标中读取数据
16 while (@@FETCH_STATUS = 0)
17 begin
18 if @str in('inser','delet','updat')
19 begin
20
21 declare @maxid int
22 declare @dnote varchar(100)
23 declare @textid int, @text varchar(2000)
24
25 select @textid = @id
26 select @text = rtrim(note) from pingjie10 where id = @id
27
28
29 select @maxid = MAX(id) from pingjie10
30 select @id = isnull(MIN(id),0) from pingjie1 where id > @id
31
32 if @id < @maxid and @id != 0 --下一个id小于最大值且不为0
33 begin
34 select @dnote = substring(ltrim(substring(note,1,100)),1,5) from out_result10 where id = @id
35
36 while @dnote not in ('delet','inser','updat')
37 begin
38 select @text = @text+LTRIM(rtrim(note)) from pingjie10 where id = @id --拼接前两条
39
40 select @id = @id +1
41
42 if @id < @maxid and @id != 0
43 begin
44 select @dnote = substring(ltrim(substring(note,1,100)),1,5) from out_result10 where id = @id
45 select @id = @id + 1
46 end
47 else begin
48 select @dnote = 'delet' --标识退出while循环
49 end
50 end
51
52 end
53
54 insert into pj_result10(id,note) select @textid,@text
55
56 end
57
58 fetch next from cursor_del1 into @id, @str
59
60
61 end
62
63
64
65
66 --关闭游标
67 close cursor_del1
68
69 --删除游标
70 deallocate cursor_del1
71
72
73
结果如下:
第三步的拼接txt中几行条件数据到一行中,好像只能拼接2行,3行的不清楚怎么不会出来.
难道是while语句有问题?这个以后有时间再回来修改修改...
