一个用于复制整个数据库的存储过程
---------------
Create Proc cpy asSet Xact_abort onBegin tran
declare @tbname nvarchar(128)declare tbname_cursor cursor for select [name] from sysobjects where type='u' Open tbname_cursor
Fetch next from tbname_cursor into @tbname
While @@FETCH_STATUS = 0
Begin
truncate table @tbname insert into @tbname
select * from server1.db1.dbo.@tbnameFetch next from tbname_cursor into @tbname
END
CLOSE tbname_cursor
DEALLOCATE tbname_cursorCommit Tran------------------------
本地服务器和server1中的表结构完全一样老是提示这几句有错误,(一是@tbname变量没有申明,而是说@tbname附近有语法错误)
delete from @tbnameinsert into @tbname
select * from server1.db1.dbo.@tbname请问怎么回事,不能这么写?
---------------
Create Proc cpy asSet Xact_abort onBegin tran
declare @tbname nvarchar(128)declare tbname_cursor cursor for select [name] from sysobjects where type='u' Open tbname_cursor
Fetch next from tbname_cursor into @tbname
While @@FETCH_STATUS = 0
Begin
truncate table @tbname insert into @tbname
select * from server1.db1.dbo.@tbnameFetch next from tbname_cursor into @tbname
END
CLOSE tbname_cursor
DEALLOCATE tbname_cursorCommit Tran------------------------
本地服务器和server1中的表结构完全一样老是提示这几句有错误,(一是@tbname变量没有申明,而是说@tbname附近有语法错误)
delete from @tbnameinsert into @tbname
select * from server1.db1.dbo.@tbname请问怎么回事,不能这么写?
truncate table @tbname
insert into @tbname
select * from server1.db1.dbo.@tbname
改为:
exec('truncate table ' + @tbname)
exec('insert into ' + @tbname + ' select * from server1.db1.dbo.' + @tbname)
exec('select * from server1.db1.dbo.'+@tbname)