整个语句没有什么问题,我想关键出在 exec('select * into temp1 from '+@sourceDBName+'..UserInfoTable where userType=4') alter table temp1 add nid int IDENTITY(1,1)你改成 exec('select nid=identity(int,1,1),* into temp1 from '+@sourceDBName+'..UserInfoTable where userType=4')就不要下面一句试试
我做了个同类型的过程,你仔细看看吧!问题可能出在你在库的之间转换上。create table t (col int,col2 varchar(20))insert t select 1,'22' union all select 1,'23' union all select 1,'24' union all select 2,'34' union all select 3,'45' go create proc t_go @did int, @sourceName sysname, @targetName sysname as begin declare @i int, @count int,@sql varchar(400) exec('select nid=identity(int,1,1),* into temp1 from '+@sourceName+'.dbo.t where col=1') exec('use '+@sourceName+'') select @count=max(nid) from temp1 exec('create table '+@targetName+'.dbo.t (col int,col2 varchar(20))') set @i=1 while @i<=@count begin set @sql='insert into '+@targetName+'.dbo.t select col,col2 from temp1 where nid='+rtrim(@i) exec(@sql) set @i=@i+1 end exec('select * from '+@targetName+'.dbo.t') exec('drop table '+@targetName+'.dbo.t,'+@sourceName+'.dbo.temp1') end go exec dbo.t_go 1,'master','samfeng' drop proc t_go drop table t(所影响的行数为 5 行) (所影响的行数为 3 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)col col2 ----------- -------------------- 1 22 1 23 1 24(所影响的行数为 3 行)
exec('select nid=identity(int,1,1),* into temp1 from '+@sourceDBName+'..UserInfoTable where userType=4')就不要下面一句试试
print @sql 可以打印出来
执行之前的[print 'i:' + rtrim(@i)]可以打印,但[print 'i2:' +rtrim(@i)]不能打印
print 'i:' + rtrim(@i)
print @sql
--exec(@sql)
print 'i2:' +rtrim(@i)
set @i=@i+1
注释掉exec,没有跳出循环,执行了10次
(col int,col2 varchar(20))insert t
select 1,'22' union all
select 1,'23' union all
select 1,'24' union all
select 2,'34' union all
select 3,'45'
go
create proc t_go
@did int,
@sourceName sysname,
@targetName sysname
as
begin
declare @i int,
@count int,@sql varchar(400)
exec('select nid=identity(int,1,1),* into temp1 from '+@sourceName+'.dbo.t where col=1')
exec('use '+@sourceName+'')
select @count=max(nid) from temp1
exec('create table '+@targetName+'.dbo.t (col int,col2 varchar(20))')
set @i=1
while @i<=@count
begin
set @sql='insert into '+@targetName+'.dbo.t
select col,col2 from temp1 where nid='+rtrim(@i)
exec(@sql)
set @i=@i+1
end
exec('select * from '+@targetName+'.dbo.t')
exec('drop table '+@targetName+'.dbo.t,'+@sourceName+'.dbo.temp1') end
go
exec dbo.t_go 1,'master','samfeng'
drop proc t_go
drop table t(所影响的行数为 5 行)
(所影响的行数为 3 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)col col2
----------- --------------------
1 22
1 23
1 24(所影响的行数为 3 行)