数据库迁移-甘肃号百

最新推荐文章于 2024-09-21 11:30:16 发布

navy887

最新推荐文章于 2024-09-21 11:30:16 发布

阅读量916

点赞数

文章标签：数据库 sap mobile login user sql

本文链接：https://blog.csdn.net/navy887/article/details/4841453

版权

/*
作用：甘肃通信助理数据库迁移--从202迁移到205
李海军
2009年11月19日
*/

--在202备份数据库
Backup database talkins to disk='d:/talkins.bak'

--备份数据库拷贝到205
--在205还原数据库
Restore database talkins from disk='d:/talkins.bak'

--报告孤立用户
Use talkins
EXEC sp_change_users_login 'REPORT'

--添加登录,设置密码,默认数据库talkins
EXEC sp_addlogin 'xuxiaoxiao','xugansuxiao','talkins'
EXEC sp_addlogin 'sap','trasin8848','talkins'
EXEC sp_addlogin 'sap1','haobai8848','talkins'

--修复孤立用户
EXEC sp_change_users_login 'UPDATE_ONE','xuxiaoxiao','xuxiaoxiao'
EXEC sp_change_users_login 'UPDATE_ONE','sap','sap'
EXEC sp_change_users_login 'UPDATE_ONE','sap','sap'

--导数据
--删除已经注销的用户
Declare
@Sql varchar(2000),
@i int
Set @i=0
While @i<10
BEGIN
Set @Sql=
  'Delete from ms_user_'+cast(@i as varchar(10))+
  ' Where mobile not in
  (Select mobile from opendatasource (''SQLOLEDB'',''Data Source=10.0.5.202;uid=sa;pwd=trasin'').talkins.dbo.ms_user_'+cast(@i as varchar(10))+')'
--Exec (@Sql)
Print @Sql
Print ''
Set @i=@i+1
END
--导入新注册用户
Declare
@Sql varchar(2000),
@i int
Set @i=0
While @i<10
BEGIN
Set @Sql=
  'Insert into ms_user_'+cast(@i as varchar(10))+'(city,createtime,FreeTime,handyphone,IsFree,isweather,leavewordflag,mobile,newphoneflag,password,prefield1,prefield2,productId,publictime,regType,remark,serviceType,smsflag,status,telephone,usersource,usertype)
  Select city,createtime,FreeTime,handyphone,IsFree,isweather,leavewordflag,mobile,newphoneflag,password,prefield1,prefield2,productId,publictime,regType,remark,serviceType,smsflag,status,telephone,usersource,usertype
  From opendatasource (''SQLOLEDB'',''Data Source=10.0.5.202;uid=sa;pwd=trasin'').talkins.dbo.ms_user_'+cast(@i as varchar(10))+
  ' Where mobile not in (select mobile from ms_user_'+cast(@i as varchar(10))+')'
--Exec (@Sql)
--Print @i
Print @Sql
Print ''
Set @i=@i+1
END
/*
--作用：提取表中的各个字段
--lihaijun
DECLARE @TB_NAME VARCHAR(200),@COL VARCHAR(500)
SET @TB_NAME='ms_user_1' --要查询的表名
SELECT @COL=ISNULL(@COL+',','')+[NAME] FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TB_NAME)
Print '表名:     '+@TB_NAME
Print '字段名:     '+@COL
*/