当前位置:Gxlcms > mysql > 创建存储过程,事务,游标,将一个表中的数据转入到另外一个库的一个表或两个表中

创建存储过程,事务,游标,将一个表中的数据转入到另外一个库的一个表或两个表中

时间:2021-07-01 10:21:17 帮助过:14人阅读

--如存在跨库链接服务器,则删除IF EXISTS(SELECT * FROM master..sysservers WHERE srvname= 'srv_lnk')EXEC sp_dropserver 'srv_lnk', 'droplogins'--建立跨库链接服务器EXEC sp_addlinkedserver 'srv_lnk','','SQLOLEDB','172.16.14.55'--最后一个参数为数

--如存在跨库链接服务器,则删除 IF EXISTS(SELECT * FROM master..sysservers WHERE srvname= 'srv_lnk') EXEC sp_dropserver 'srv_lnk', 'droplogins' --建立跨库链接服务器 EXEC sp_addlinkedserver 'srv_lnk','','SQLOLEDB','172.16.14.55'--最后一个参数为数据库服务器地址 EXEC sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','sa'--最后两个个参数为数据库服务器的登录用户名和密码 id FROM ChinaHRD.dbo.users myCursor myCursor (EMall.dbo.mall_Users AS nUsers WHERE nUsers.Name = (SELECT PetName FROM ChinaHRD.dbo.users oUsers WHERE id=@id)) myTran EMall.dbo.mall_Users(Id,Name, Email,PasswordFormat, Password,UCenterId,CreationTime,LastLoginTime,IsApproved,IsLockedOut,LastActivityTime, LastPasswordChangedTime,LastLockoutTime,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart,Revenue,RmbBalance) ,,, ,,0.0,0.0 FROM ChinaHRD.dbo.users --向扩展表导入数据 INSERT TableName(ArticleID, XueKe, KanMing,JuanQi, CaiJiWangZhi, CaiJiShiJian,FuJian) SELECT @MaxID,学科,发者,发时,采址,采时, '/attachment/'+附件 FROM OldTableName WHERE id=@id myTranmyTranmyCursor myCursor DEALLOCATE myCursor /* exec sp_dropserver 'srv_lnk', 'droplogins'--删除跨库链接服务器

人气教程排行