不,我的意思不是用你的游标,就用我上面的两句话.你用游标也可以,但是我觉得没什么必要,而且你这里有一句致命的错误. 在游标内循环里面用了 delete from Test_user1 insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid ) select User_Id ,User_Name ,Full_Name ,PassWord ,roleid from Test_user 等于是插入一条,再删除一条,循环到最后,test_user1里面只盛夏最后一条记录. ^_^
如果你有你非要用游标的理由那么把delete from test_user1 提到循环外面.或者给delete加条件 比如 ALTER PROCEDURE [test] as declare @qybm varchar(22) set nocount on set xact_abort on begin begin tran declare xin_cur cursor for select roleid from Test_user open xin_cur fetch next from xin_cur into @qybm while @@fetch_status = 0 begin delete from Test_user1 where roleid=@qybm insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid ) select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,'test' from Test_user where roleid=@qybm fetch next from xin_cur into @qybm end commit tran close xin_cur deallocate xin_cur end
假如要将test_user表中roleid为1的多条记录插入到test_user1中,不用游标的话,还有其他什么方法吗?这样可以吗?delete from test_user1 where roleid=1 insert into test_user1 select User_ID,User_Name,Full_Name,PassWord,roleid,'test' from test_user where roleid=1或者 if not exists(select 1 from test_user1 where roleid=1) insert into test_user1 select User_ID,User_Name,Full_Name,PassWord,roleid,'test' from test_user where roleid=1
老大: 我这么写的: begin if exists(select * from Test_user1 where roleid = @qybm) delete from Test_user1 where roleid = @qybm else insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,test) select User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,''+ @qybm+' ' from Test_user fetch next from xin_cur into @qybm end test_user表中有2条记录,roleid分别是1和2,我执行后应该是将test_user中的2条数据全部插入到test_user1中的,但是却是每执行一次插入一次,执行到第3次时却将test_user1的数据全删除了。为什么啊,哪里写错了?
--当中的else不应该要的吧 begin if exists(select * from Test_user1 where roleid = @qybm) delete from Test_user1 where roleid = @qybm --else insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,test) select User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,''+ @qybm+' ' from Test_user fetch next from xin_cur into @qybm end
重新写一个 ALTER PROCEDURE [test] as declare @qybm varchar(22) set nocount on set xact_abort on begin begin tran declare xin_cur cursor for select roleid from Test_user open xin_cur fetch next from xin_cur into @qybm while @@fetch_status = 0 begin if exists(select * from Test_user1 where roleid=@qybm) delete from Test_user1 where roleid=@qybm insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid ) select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,@qybm from Test_user where roleid=@qybm fetch next from xin_cur into @qybm end commit tran close xin_cur deallocate xin_cur end
--改一下,少加一个test字段ALTER PROCEDURE [test] as declare @qybm varchar(22) set nocount on set xact_abort on begin begin tran declare xin_cur cursor for select roleid from Test_user open xin_cur fetch next from xin_cur into @qybm while @@fetch_status = 0 begin if exists(select * from Test_user1 where roleid=@qybm) delete from Test_user1 where roleid=@qybm insert into Test_user1 select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,@qybm from Test_user where roleid=@qybm fetch next from xin_cur into @qybm end commit tran close xin_cur deallocate xin_cur end
--另外也可以这样 begin tran delete from Test_user1 insert into Test_user1 select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,roleid from Test_user commit tran
可以不用游标实现楼主的要求呀 Declare @qybm int select @qybm=min(roleid) From Test_user while not @qybm is null begin detele from Test_user1 where roleid=@qybm insert into Test_user1 select a.*,'Test' from test_user where roleid=@qybm
select @qybm=min(roleid) From Test_user where roleid>@qybm end
那么
delete from test_user1
insert into test_user1
select User_ID,User_Name,Full_Name,PassWord,roleid,'test'
from test_user
我这么试了,但是@qybm却是roleid的最后一条记录,是为什么啊老大
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,''+ @qybm+' ' from Test_user
在游标内循环里面用了
delete from Test_user1
insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid )
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid from Test_user 等于是插入一条,再删除一条,循环到最后,test_user1里面只盛夏最后一条记录. ^_^
比如
ALTER PROCEDURE [test]
as
declare @qybm varchar(22)
set nocount on
set xact_abort on
begin
begin tran
declare xin_cur cursor
for
select roleid from Test_user
open xin_cur
fetch next from xin_cur into @qybm
while @@fetch_status = 0
begin
delete from Test_user1 where roleid=@qybm
insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid )
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,'test' from Test_user
where roleid=@qybm
fetch next from xin_cur into @qybm
end
commit tran
close xin_cur
deallocate xin_cur
end
insert into test_user1
select User_ID,User_Name,Full_Name,PassWord,roleid,'test'
from test_user where roleid=1或者
if not exists(select 1 from test_user1 where roleid=1)
insert into test_user1
select User_ID,User_Name,Full_Name,PassWord,roleid,'test'
from test_user where roleid=1
我这么写的:
begin
if exists(select * from Test_user1 where roleid = @qybm)
delete from Test_user1 where roleid = @qybm
else
insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,test)
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,''+ @qybm+' ' from Test_user
fetch next from xin_cur into @qybm
end
test_user表中有2条记录,roleid分别是1和2,我执行后应该是将test_user中的2条数据全部插入到test_user1中的,但是却是每执行一次插入一次,执行到第3次时却将test_user1的数据全删除了。为什么啊,哪里写错了?
begin
if exists(select * from Test_user1 where roleid = @qybm)
delete from Test_user1 where roleid = @qybm
--else
insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,test)
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid ,''+ @qybm+' ' from Test_user fetch next from xin_cur into @qybm
end
ALTER PROCEDURE [test]
as
declare @qybm varchar(22)
set nocount on
set xact_abort on
begin
begin tran
declare xin_cur cursor
for
select roleid from Test_user
open xin_cur
fetch next from xin_cur into @qybm
while @@fetch_status = 0
begin
if exists(select * from Test_user1 where roleid=@qybm)
delete from Test_user1 where roleid=@qybm
insert into Test_user1(User_Id ,User_Name ,Full_Name ,PassWord ,roleid )
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,@qybm from Test_user
where roleid=@qybm
fetch next from xin_cur into @qybm
end
commit tran
close xin_cur
deallocate xin_cur
end
as
declare @qybm varchar(22)
set nocount on
set xact_abort on
begin
begin tran
declare xin_cur cursor
for
select roleid from Test_user
open xin_cur
fetch next from xin_cur into @qybm
while @@fetch_status = 0
begin
if exists(select * from Test_user1 where roleid=@qybm)
delete from Test_user1 where roleid=@qybm
insert into Test_user1
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,@qybm from Test_user
where roleid=@qybm
fetch next from xin_cur into @qybm
end
commit tran
close xin_cur
deallocate xin_cur
end
begin tran
delete from Test_user1
insert into Test_user1
select User_Id ,User_Name ,Full_Name ,PassWord ,roleid,roleid from Test_user
commit tran
Declare @qybm int
select @qybm=min(roleid) From Test_user
while not @qybm is null
begin
detele from Test_user1 where roleid=@qybm
insert into Test_user1
select a.*,'Test' from test_user where roleid=@qybm
select @qybm=min(roleid) From Test_user
where roleid>@qybm
end
问题解决。
揭贴。
以后还请多多指点:)