请教这个存储过程应如何写? ( 积分: 100 )

  • 主题发起人 主题发起人 lifndcw
  • 开始时间 开始时间
L

lifndcw

Unregistered / Unconfirmed
GUEST, unregistred user!
我有3个表,&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;t1&nbsp;&nbsp;&nbsp;(表名)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;id&nbsp;&nbsp;&nbsp;(字段)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;subject1&nbsp;&nbsp;&nbsp;(字段)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;subject2&nbsp;&nbsp;&nbsp;(字段)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;t2&nbsp;&nbsp;&nbsp;(表名)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;id&nbsp;&nbsp;&nbsp;(字段)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;t1_id&nbsp;&nbsp;&nbsp;(字段)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;subject1&nbsp;&nbsp;&nbsp;(字段)&nbsp;&nbsp;&nbsp;<br><br>&nbsp;t3&nbsp;&nbsp;&nbsp;(表名)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;id&nbsp;&nbsp;&nbsp;(字段)&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;t2_id&nbsp;&nbsp;&nbsp;(字段)&nbsp;&nbsp;&nbsp;<br>&nbsp;subject2&nbsp;&nbsp;&nbsp;(字段)<br>要求将T1表中的数据按下面要求填入T2,T3<br>t2.t1_id=t1.id<br>t2.subject1=t1.subject1&nbsp;<br><br>t3.t2_id=t2.id<br>t3.subject2=t1.subject2
 
declare&nbsp;@id&nbsp;int,&nbsp;@subject1&nbsp;varchar(100),&nbsp;@subject2&nbsp;varchar(100),&nbsp;@t1_id&nbsp;int<br>declare&nbsp;cur1&nbsp;cursor&nbsp;for&nbsp;select&nbsp;*&nbsp;from&nbsp;t1<br>open&nbsp;cur1<br>fetch&nbsp;next&nbsp;from&nbsp;cur1&nbsp;into&nbsp;@id,&nbsp;@subject1,&nbsp;@subject2<br>while&nbsp;@@fetch_status=0<br>begin<br>&nbsp;&nbsp;insert&nbsp;into&nbsp;t2&nbsp;(t1_id,&nbsp;subject1)&nbsp;values&nbsp;(@id,&nbsp;@subject1)<br>&nbsp;&nbsp;set&nbsp;@t1_id=@@Identity<br>&nbsp;&nbsp;insert&nbsp;into&nbsp;t3&nbsp;(t2_id,&nbsp;subject2)&nbsp;values&nbsp;(@t1_id,&nbsp;@subject2)<br>&nbsp;&nbsp;fetch&nbsp;next&nbsp;from&nbsp;cur1&nbsp;into&nbsp;@id,&nbsp;@subject1,&nbsp;@subject2<br>end<br>close&nbsp;cur1<br>deallocate&nbsp;cur1<br><br>其中,第一句,@subject1,&nbsp;@subject2类型自己改。
 
游标遍历表。回答挺快的。
 
来个不用游标的:<br>······<br>insert&nbsp;into&nbsp;t2(t1_id,&nbsp;subject1)&nbsp;<br>select&nbsp;t1.id,t1.subject1&nbsp;from&nbsp;t1,t2&nbsp;<br>where&nbsp;t1.id=t2.t1_id&nbsp;and&nbsp;t1.subject1=t2.subject1&nbsp;<br><br>insert&nbsp;into&nbsp;t3(t2_id,&nbsp;subject2)&nbsp;<br>select&nbsp;t1.id,t1.subject2&nbsp;from&nbsp;t1,t3&nbsp;<br>where&nbsp;t1.id=t3.t2_id&nbsp;and&nbsp;t1.subject2=t3.subject2&nbsp;<br>······
 
尽量不要游标&nbsp;lovedesky&nbsp;的方法不错
 
后退
顶部