对sql server里的数据做BCP导出工作:在命令行下进行操作：
- 关于ACCU的导出

bcp "select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.ACCU_Daily_2007 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.ACCU_Daily_2008 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.ACCU_Daily_2009 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg  from GEKPI.dbo.ACCU_Daily_2010 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.ACCU_Daily_2009 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.ACCU_Daily_2010 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2007 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2008 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2009 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2010 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,accu,case when accuAvg is NULL then 0 else accuAvg end from [9Z_KPI].dbo.Nw_CharCount2009 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,accu,case when accuAvg is NULL then 0 else accuAvg end from [9Z_KPI].dbo.Nw_CharCount2010" queryout c:\test1.bat -c -T
导出了64181条数据。
**关于PCCU的导出
bcp "select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.PCCU_Daily_2007 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.PCCU_Daily_2008 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.PCCU_Daily_2009 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg  from GEKPI.dbo.PCCU_Daily_2010 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.PCCU_Daily_2009 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.PCCU_Daily_2010 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2007 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2008 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2009 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2010 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,pccu,0 from [9Z_KPI].dbo.Nw_CharCount2009 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,pccu,0 from [9Z_KPI].dbo.Nw_CharCount2010" queryout c:\test2.bat -c -T

导出了64181条数据。

建立分区表
- ACCU脚本：

CREATE TABLE ACCU_Daily_His
(
site_cd CHAR(5),
create_ts DATE,
site_id CHAR(4),
GROUP_ID VARCHAR(4),
accu_val NUMBER(10),
accu_val_avg NUMBER(10)
)
PARTITION BY RANGE(create_ts)
( 
	PARTITION ACCU_Daily_His_before_2004 VALUES less than (TO_DATE('20040101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2004 VALUES less than (TO_DATE('20050101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2005 VALUES less than (TO_DATE('20060101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2006 VALUES less than (TO_DATE('20070101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2007 VALUES less than (TO_DATE('20080101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2008 VALUES less than (TO_DATE('20090101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2009 VALUES less than (TO_DATE('20100101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2010 VALUES less than (TO_DATE('20110101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2011 VALUES less than (TO_DATE('20120101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2012 VALUES less than (TO_DATE('20130101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2013 VALUES less than (TO_DATE('20140101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2014 VALUES less than (TO_DATE('20150101','yyyymmdd'))
);

- PCCU脚本：

CREATE TABLE PCCU_Daily_His
(
site_cd CHAR(5),
create_ts DATE,
site_id CHAR(4),
GROUP_ID VARCHAR(4),
pccu_val NUMBER(10),
pccu_val_avg NUMBER(10)
)
PARTITION BY RANGE(create_ts)
(
 
        PARTITION p_PCCU_His_Before_2004 VALUES less than (TO_DATE('20040101','yyyymmdd')),
	PARTITION p_PCCU_His_2004 VALUES less than (TO_DATE('20050101','yyyymmdd')),
	PARTITION p_PCCU_His_2005 VALUES less than (TO_DATE('20060101','yyyymmdd')),
	PARTITION p_PCCU_His_2006 VALUES less than (TO_DATE('20070101','yyyymmdd')),
	PARTITION p_PCCU_His_2007 VALUES less than (TO_DATE('20080101','yyyymmdd')),
	PARTITION p_PCCU_His_2008 VALUES less than (TO_DATE('20090101','yyyymmdd')),
	PARTITION p_PCCU_His_2009 VALUES less than (TO_DATE('20100101','yyyymmdd')),
	PARTITION p_PCCU_His_2010 VALUES less than (TO_DATE('20110101','yyyymmdd')),
	PARTITION p_PCCU_His_2011 VALUES less than (TO_DATE('20120101','yyyymmdd')),
	PARTITION p_PCCU_His_2012 VALUES less than (TO_DATE('20130101','yyyymmdd')),
	PARTITION p_PCCU_His_2013 VALUES less than (TO_DATE('20140101','yyyymmdd')),
	PARTITION p_PCCU_His_2014 VALUES less than (TO_DATE('20150101','yyyymmdd'))
);

取出test1.bat,test2.bat文件放在10.127.16.15上，并在同一路径建立控制文件input1.ctl,input2.ctl。
- input1.ctl脚本：

load data
infile 'test.bat'
append INTO TABLE KPI.ACCU_Daily_His
fields terminated BY X'09'
(site_cd,create_ts "to_date(:create_ts,'yyyy-mm-dd')",site_id,GROUP_ID,accu_val,accu_val_avg)

- input2.ctl脚本：

load data
infile 'test1.bat'
append INTO TABLE KPI.PCCU_Daily_His
fields terminated BY X'09'
(site_cd,create_ts "to_date(:create_ts,'yyyy-mm-dd')",site_id,GROUP_ID,pccu_val,pccu_val_avg)

导入数据：

在命令行下同一路径下：

sqlldr KPI/kpigamenow@orads control=input1.ctl
sqlldr KPI/kpigamenow@orads control=input2.ctl

这样就OK了。 PS: 建表的时候 site_id为char(4)需要在导入数据前修改类型为 varchar(6)。

CCU迁移