将所有用户表数据存入临时表后TRUN该表再将原数据导回的问题

[fuxiaoyang13]

我想将某数据库里所有的用户表进行如下操作；先将某用户表中的数据先保存到一个临时表中后再将该用户表TRUNCATE后 再将保存出去的数据再导回原表，请问该如何做最好？

[-狙击手-]

declare @s varchar(1000)
declare cur cursor for select name from sysobjects where xtype = 'U'
open cur
fetch cur into @s
while @@fetch_status = 0 
begin
   exec('select * into '+@s+'_temp from '+@s)
   exec('truncate table '+@s)
   exec('insert into '+@s+' select * from '+@s)
   fetch cur into @s
end
close cur
DEALLOCATE cur

[ai_li7758521]

[Quote=引用 1 楼 happyflystone 的回复:]
SQL codedeclare @s varchar(1000)
declare cur cursor for select name from sysobjects where xtype = 'U'
open cur
fetch cur into @s
while @@fetch_status = 0
begin
exec('select * into '+@s+'_temp from '+@s)
exec('truncate table '+@s)
exec('insert into '+@s+' select * from '+@s)
fetch cur into @s
end
close cur
DEALLOCATE cur
[/Quote]。

[--小F--]

[Quote=引用 1 楼 happyflystone 的回复:]
SQL codedeclare @s varchar(1000)
declare cur cursor for select name from sysobjects where xtype = 'U'
open cur
fetch cur into @s
while @@fetch_status = 0
begin
exec('select * into '+@s+'_temp from '+@s)
exec('truncate table '+@s)
exec('insert into '+@s+' select * from '+@s)
fetch cur into @s
end
close cur
DEALLOCATE cur
[/Quote]

学习

[l1ka1lz8]

declare @s varchar(1000)
declare cur cursor for select name from sysobjects where xtype = 'U'
open cur
fetch cur into @s
while @@fetch_status = 0 
begin
   exec('select * into '+@s+'_temp from '+@s)--这里的是@s+_temp
   exec('truncate table '+@s)
   exec('insert into '+@s+' select * from '+@s)--这里怎么又是@s，有点迷糊，错了？
   fetch cur into @s
end
close cur
DEALLOCATE cur

学习！

[fuxiaoyang13]

declare @s varchar(1000)
declare cur cursor for select name from sysobjects where xtype = 'U'
open cur
fetch cur into @s
while @@fetch_status = 0
begin
exec('select * into '+@s+'_temp from '+@s)
exec('truncate table '+@s)
exec('insert into '+@s+' select * from '+@s)
fetch cur into @s
end
close cur
DEALLOCATE cur
我是这样做的啊，可做完后我发现，有些表的数据没有导回去，不知道是 啥原因

[fuxiaoyang13]

咋没人顶啊？

[fuxiaoyang13]

没人啊？？？