lyfz_repos/经典版源码/document/数据库维护脚本/复制renyuan表和user表到其他数据库.sql

-- ============================================= 
-- 程序编写： Jeff
-- 版    本:  V 1.0
-- 建立日期： 2015-05-20
-- 功能说明： 复制renyuan表和user表到其他数据库;
-- 备    注： 

-- 修改日期：
-- 修改说明：
-- ============================================= 

-- 创建临时数据库,用于存储需要的表;
create database temp 
on  primary  -- 默认就属于primary文件组,可省略
(
	/*--数据文件的具体描述--*/
    name = 'temp',						-- 主数据文件的逻辑名称
    filename = 'D:\temp.mdf',			-- 主数据文件的物理名称
    size = 2mb,							-- 主数据文件的初始大小
    maxsize = 100mb,					-- 主数据文件增长的最大值
    filegrowth = 15%					-- 主数据文件的增长率
)
log on
(
	/*--日志文件的具体描述,各参数含义同上--*/
    name = 'temp_log',
    filename = 'D:\temp_log.ldf',
    size = 2mb,
    filegrowth = 1mb
)
go

-- 将数据插入到临时数据库中;
select identity(int,1,1) as autoid,* into [temp].[dbo].[renyuan] from [ddf].[dbo].[renyuan]
select identity(int,1,1) as autoid,* into [temp].[dbo].[user] from [ddf].[dbo].[user]
go

-- 将临时数据库附加到外网分店中,重新将临时数据库的表数据转移出来;
-- user表,以account和name唯一;
-- renyuan表,以name和id唯一;
------------------------------------------------------------------
declare @r_id			nvarchar(24)			-- 员工表id;
declare @r_name			nvarchar(24)			-- 员工表姓名;
declare @r_bm			nvarchar(24)			-- 员工表部门;
declare @r_sex			nvarchar(24)			-- 员工表;
declare @r_phone		nvarchar(24)			-- 员工表;
declare @r_address		nvarchar(50)			-- 员工表;
declare @r_bz			nvarchar(max)			-- 员工表;
declare @r_base			nvarchar(24)			-- 员工表;
declare @r_rate1		nvarchar(24)			-- 员工表;
declare @r_rate2		nvarchar(24)			-- 员工表;
declare @r_bz2			nvarchar(max)			-- 员工表;
declare @r_address2		nvarchar(50)			-- 员工表;
declare @r_idno			nvarchar(50)			-- 员工表;
declare @r_marry		nvarchar(24)			-- 员工表;
declare @r_dimission	nvarchar(24)			-- 员工表;
declare @r_rate3		nvarchar(24)			-- 员工表;
declare @r_rate4		nvarchar(24)			-- 员工表;
declare @r_rate5		nvarchar(24)			-- 员工表;
declare @r_loginno1		nvarchar(24)			-- 员工表;
declare @r_loginno2		nvarchar(24)			-- 员工表;
--declare @r_photo		image					-- 员工表;
declare @r_level		nvarchar(24)			-- 员工表;
----------------------------------------------------------------
declare @Index			int						-- 索引值;
declare @count			int						-- 数量;

-- 先处理renyuan表;
select @Index = count(*) from [temp].[dbo].[renyuan]
while(@Index > 0)
begin
	select	@r_id = id, 
			@r_name = [name],
			@r_bm = bm,
			@r_sex = sex,	
			@r_phone = phone,
			@r_address = [address],
			@r_bz=bz,	
			@r_base	= base,	
			@r_rate1 = rate1,
			@r_rate2 = rate2,
			@r_bz2 = bz2,
			@r_address2	= address2,
			@r_idno	= idno,
			@r_marry = marry,
			@r_dimission = dimission,
			@r_rate3 = rate3,
			@r_rate4 = rate4,
			@r_rate5 = rate5,
			@r_loginno1 = loginno1,
			@r_loginno2	= loginno2,
			@r_level = [level]
	from [temp].[dbo].[renyuan] where autoid = @Index

	select @count = count(*) from [ddf].[dbo].[renyuan] where id = @r_id and [name] = @r_name
	if( @count = 0 )
		insert into [ddf].[dbo].[renyuan]
			([bm]
           ,[id]
           ,[name]
           ,[sex]
           ,[phone]
           ,[address]
           ,[bz]
           ,[base]
           ,[rate1]
           ,[rate2]
           ,[bz2]
           ,[address2]
           ,[idno]
           ,[marry]
           ,[dimission]
           ,[rate3]
           ,[rate4]
           ,[rate5]
           ,[loginno1]
           ,[loginno2]
           ,[level])values
			(@r_bm,@r_id,@r_name,@r_sex,@r_phone,@r_address,@r_bz,@r_base,
			@r_rate1,@r_rate2,@r_bz2,@r_address2,@r_idno,@r_marry,@r_dimission,
			@r_rate3,@r_rate4,@r_rate5,@r_loginno1,@r_loginno2,@r_level)
	print @count
	set @Index = @Index - 1
end
go

------------------------------------------------------------------
declare @u_account		nvarchar(24)			-- 用户表账号;
declare @u_name			nvarchar(24)			-- 用户表姓名;
declare @u_psw			nvarchar(50)			-- 用户表;
declare @u_rights		nvarchar(50)			-- 用户表;
declare @u_rate1		nvarchar(50)			-- 用户表;
declare @u_rate2		nvarchar(50)			-- 用户表;
declare @u_base			nvarchar(50)			-- 用户表;
declare @u_bz			nvarchar(50)			-- 用户表;
declare @u_rights2		nvarchar(50)			-- 用户表;
declare @u_discount		nvarchar(50)			-- 用户表;
declare @u_discount2	nvarchar(50)			-- 用户表;
------------------------------------------------------------------
declare @Index			int						-- 索引值;
declare @count			int						-- 数量;
------------------------------------------------------------------
select @Index = count(*) from [temp].[dbo].[user]
while( @Index > 0)
begin
	select 
	@u_account = account, 
	@u_name	= [name],
	@u_psw	= psw,
	@u_rights = rights,
	@u_rate1 = rate1,
	@u_rate2 = rate2,
	@u_base	= base,
	@u_bz = bz,
	@u_rights2 = rights2,	
	@u_discount	= discount,
	@u_discount2 = discount2
	from [temp].[dbo].[user] where autoid = @Index

	select @count = count(*) from [ddf].[dbo].[user] where account = @u_account and [name] = @u_name
	if ( @count = 0 )
		insert into [ddf].[dbo].[user]
		([account]
           ,[name]
           ,[psw]
           ,[rights]
           ,[rate1]
           ,[rate2]
           ,[base]
           ,[bz]
           ,[rights2]
           ,[discount]
           ,[discount2]) values
		(@u_account,@u_name,@u_psw,@u_rights,@u_rate1,@u_rate2,@u_base,@u_bz,@u_rights2,@u_discount,@u_discount2) 
	print @count

	set @Index = @Index - 1
end
go