sql server 2000 一个表的数据插入到三个相关联表中,一条一条的插入
1
/**********************************************************
2
测试数据 sql server 2000 一个表的数据插入到三个相关联表中,一条一条的插入
3
---涂聚文 缔友计算机信息技术有限公司 软件工程师 geovindu@163.com www.dupcit.com
4
---捷为工作室 涂聚文 www.dusystem.com
5
**********************************************************/
6
---测试数据
7
--生成测试数据表结构
8
create table A
9
(
10
a_id int identity(100,1),
11
dname varchar(30),
12
dates datetime default getdate()
13
)
14
GO
15
create table B
16
(
17
b_id int identity(10,1),
18
a_id int,
19
coutry varchar(30),
20
bdates datetime default getdate()
21
)
22
GO
23![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
create table C
26
(
27
c_id int identity(1,1),
28
b_id int,
29
ac_id int,
30
cname varchar(30),
31
cdates datetime default getdate()
32
)
33
go
34
create table E
35
(
36
E_id int identity(11000,1),
37
E_a varchar(30),
38
E_b varchar(30),
39
E_c varchar(30),
40
edates datetime default getdate()
41
)
42
GO
43![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
insert into E(E_a,E_b,E_c)
45
select 'A5','B5','C5'
46
union all select 'A6','B6','C6'
47
union all select 'A7','B7','C7'
48
union all select 'A8','B8','C8'
49
union all select 'A9','B9','C9'
50
union all select 'A10','B10','C10'
51![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
--创建触发器
53![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
drop trigger trg_A
55
go
56
create trigger trg_A
57
on A
58
for insert
59
as
60
begin
61
insert into B(a_id) select a_id from inserted
62
insert into C(b_id) select @@identity
63
end
64
go
65
66
--向A表插入数据,实现触发
67
insert into A(a_id) select 1
68
69
--查看触发器执行结果
70
select * from A
71
select * from B
72
select * from C
73
74![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
/*---创建事务
76
geovindu@163.com 缔友计算机信息技术有限公司
77
涂聚文 www.dusystem.com
78
*/
79![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
GO
81![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
CREATE PROCEDURE sp_InsertMm
83
@dname varchar(20),
84
@coutry nvarchar(50),
85
@cname varchar(50)
86
AS
87
DEClARE @AID int,@BID int
88
-- 创建事务Mm
89
BEGIN TRANSACTION InsertSome
90
INSERT INTO A (dname)
91
VALUES (@dname)
92
SET @AID = @@IDENTITY -- 得到刚才增加的Id
93
INSERT INTO B (a_id, coutry)
94
VALUES (@AID,@coutry)
95
SET @BID = @@IDENTITY
96
INSERT INTO C (b_id,ac_id,cname)
97
VALUES (@BID,@AID,@cname)
98
99
-- 如果有错误,回滚事务,否则提交事务
100
IF @@ERROR > 0
101
ROLLBACK TRANSACTION InsertSome
102
ELSE
103
COMMIT TRANSACTION InsertSome
104
GO
105![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
exec sp_InsertMm 'A2','B2','C2'
107![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
select * from A
109
select * from B
110
select * from C
111
select * from E
112
--删除测试环境
113
drop trigger trg_A
114
drop table A,B,C,E
115
drop procedure sp_InsertMm
116
drop procedure sp_InsertMm1
117![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
118
--循环的话,你可以用while,也可以用go,如:
119
--1)
120
CREATE PROCEDURE sp_InsertMm1
121
as
122
declare @i int,@count int
123
select @count=count(*) from E
124
-- select @count
125
126
set @i = 11000
127
while @i<=11000+@count
128
begin
129
--操作语句
130
DECLARE @dname varchar(20),@coutry nvarchar(50),@cname varchar(50)
131
DEClARE @AID int,@BID int
132
SELECT @dname=E_a FROM E where E_id=@i
133
SELECT @coutry=E_b FROM E where E_id=@i
134
SELECT @cname=E_c FROM E where E_id=@i
135
-- 创建事务Mm
136
BEGIN TRANSACTION InsertSome
137
INSERT INTO A (dname)
138
VALUES (@dname)
139
SET @AID = @@IDENTITY -- 得到刚才增加的Id
140
INSERT INTO B (a_id, coutry)
141
VALUES (@AID,@coutry)
142
SET @BID = @@IDENTITY
143
INSERT INTO C (b_id,ac_id,cname)
144
VALUES (@BID,@AID,@cname)
145
-- 如果有错误,回滚事务,否则提交事务
146
IF @@ERROR > 0
147
ROLLBACK TRANSACTION InsertSome
148
ELSE
149
COMMIT TRANSACTION InsertSome
150
set @i=@i+1
151
end
152
Go
153![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
154
exec sp_InsertMm1
155
156
--2)
157
declare @i int
158
set @i=0
159
xloop:--标签
160
--操作
161
set @i=@i+1
162
163
if @i<100 --判断,当@i小于100的时候,回到操作执行处,继续执行
164
goto xloop
165![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
166
/**********************************************************
167
测试数据一个表的数据插入到三个相关联表中,一条一条的插入
168
---涂聚文 缔友计算机信息技术有限公司 软件工程师 geovindu@163.com www.dupcit.com
169
---捷为工作室 涂聚文 www.dusystem.com
170
**********************************************************/
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
130
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
131
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
132
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
133
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
134
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
135
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
136
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
137
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
138
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
139
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
140
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
141
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
142
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
143
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
144
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
145
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
146
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
147
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
148
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
149
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
150
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
151
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
152
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
153
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
154
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
155
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
156
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
157
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
158
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
159
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
160
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
161
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
162
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
163
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
164
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
165
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
166
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
167
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
168
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
169
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
170
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)