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
