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

CREATE PROCEDURE dataMove_mall_users  --创建存储过程

--ALTER procedure dataMove_mall_users --修改存储过程

AS

BEGIN

DECLARE @MaxID INT --插入到表后的新ID

DECLARE @Count INT --统计迁移数据的条数

SET @Count=0

/*
--如存在跨库链接服务器,则删除

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'--最后两个个参数为数据库服务器的登录用户名和密码

 */

DECLARE myCursor  CURSOR FOR

SELECT id FROM ChinaHRD.dbo.users

DECLARE @id INT

OPEN myCursor

FETCH NEXT FROM myCursor INTO @id

WHILE @@fetch_status = 0

BEGIN

IF NOT EXISTS(SELECT * FROM EMall.dbo.mall_Users AS nUsers WHERE nUsers.Name = (SELECT PetName FROM ChinaHRD.dbo.users oUsers WHERE id=@id))

BEGIN

BEGIN TRAN myTran --开始执行事务

--向文章表导入数据

INSERT EMall.dbo.mall_Users(Id,Name, Email,PasswordFormat, Password,UCenterId,CreationTime,LastLoginTime,IsApproved,IsLockedOut,LastActivityTime,
    LastPasswordChangedTime,LastLockoutTime,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,
    FailedPasswordAnswerAttemptWindowStart,Revenue,RmbBalance) 

SELECT id,petname,Name,0,Password,id,RregTime,LoginTime,0,0,'0001-01-01 00:00:00.0000000','0001-01-01 00:00:00.0000000','0001-01-01 00:00:00.0000000',
    0,'0001-01-01 00:00:00.0000000',0,'0001-01-01 00:00:00.0000000',0.0,0.0

FROM ChinaHRD.dbo.users

WHERE id=@id


SET @MaxID=@@identity --得到最新插入记录的ID

--print @MaxID

 
/*
--向扩展表导入数据

INSERT TableName(ArticleID, XueKe, KanMing,JuanQi, CaiJiWangZhi, CaiJiShiJian,FuJian) 

SELECT @MaxID,学科,发者,发时,采址,采时, '/attachment/'+附件

FROM OldTableName

WHERE id=@id
*/
 

SET @Count=@Count+1

 

IF @@error<>0 --判断如果两条语句有任何一条出现错误

BEGIN

ROLLBACK TRAN myTran--开始执行事务的回滚，恢复的转账开始之前状态

END

ELSE  --如何两条都执行成功

BEGIN

COMMIT TRAN myTran--执行这个事务的操作

END
END --END if

FETCH NEXT FROM  myCursor INTO @id

END--end while

CLOSE myCursor

DEALLOCATE myCursor

 
 /*
exec sp_dropserver  'srv_lnk', 'droplogins'--删除跨库链接服务器

 */

 

RETURN @Count --返回导入记录条数

END

--测试存储过程

--declare @return_status int

--exec @return_status= dataMove_mall_users

--print @return_status