SQL远程数据同步与数据导入的常用方法[原创]

ehxz

//blog.csdn.net.chengg0769 转载此行保留 QQ群请加: 6539042(powerbuilder11&SQL)   
在大量数据传递过程，我们经常遇到txt文本到table的import和export 。txt既是通用的形式，也常常是系统之间隔离的最好方法。

这里我们讲如何实现本地数据库和远程数据库的同步。并且达到无人干预，几乎自动化。我的写法和做法不一定正确，首先申明。

1. 远程数据自动定时export到FTP上

2. 本地安装CuteFTP软件，设置帐号密码，然后设置两个文件夹同步。自然是本地同步于远程。同步规格为“只要文件更新日期比现在的新则覆盖”。

3. 计算时间差，即远程文件导出文本的时间点，下载到本地需要的时间，然后在本地设置最合适的启动作业时间。

4. txt文件导入方式，如果是表名固定的话，将remote file的存放目录固定下来，然后在DTS服务里设置DTS包。如123.dts，需要存为文件，便于你拷贝到其它地方。注意：如果拷贝到其它地方时文件路径改变需要编辑修改DTS包；编辑后需要另存一份新的，这样你在调用时不必指名包的版本，比较方便，否则不指名版本的调用会报错。

如果表名和文件名不固定，则唯一的方法是设置procedure来进行处理，使用动态语句来创建新表或分表，然后根据动态的文件名导入相应的文件到对应的数据表里。

这个是比较麻烦的，因为主要是存在数据的分表，有些数据是按月，按旬导出的，就要根据系统计算当前所在的周期，而且要判断当前的日期，看应该导入哪个文件了。当然，执行DTS,bulk insert 语句之前都需要这个文件存在，否则执行会中断。

动态语句麻烦在看起来不直接，而且语句通常比较复杂，我所接触的都在400-1200行左右，有时感觉非常烦燥。里面有大段红色的用引号引起来的语句。几乎无法查阅。非常凌乱。更糟糕的是很多人对这个根本就没有接触，很多人写procedure都很少。

动态语句总是有很多引号(原本有单引号的需改成两个单引号)，而且有许多文字拼接。有的还采用参数传入传出，看得人眼花缭乱，变动修改也是十分烦人。所以，详细的说明文字和修改备注是必须的。

判断文件或目录是否存在看下面给出的存储器，你可以直接使用。

/*判断文件是否存在--------------------------------------------------------------------------------------------------------------*/
/* drop procedure pr_filepathexists */
create procedure pr_filepathexists(@parm varchar(200),@filepath smallint,@ret char(1) ='' output)
as
CREATE TABLE #tmp (
FileExists BIT,
isDirectory BIT,
ParentExists BIT)

INSERT INTO #tmp
EXEC master.dbo.xp_fileexist @parm

SELECT @ret = case @filepath when 1 then FileExists when 2 then isDirectory when 3 then ParentExists end

FROM #tmp
select @ret
drop table #tmp
-----------------------------------------------------------------------------------------------------------------------------------------------

当然，按常理，如某个文件在你导入时应该存在，但可能远程服务器出错，未导出该文件。或者要晚一天导出，则你在执行导入时，检测到这个异常，一般应该写入日志，提示管理员处理。按旬执行的语句，你应该重复的检查几次，直到正确导入。必须得给一些宽裕度。

在procedure里，我们基本上可以直接执行DTS或者用bulk insert 来插入，效率还可以。百万级，千万级的数据也没问题。只要CPU足够快，内存足够大。

执行DTS:

SET @dtsname = @dts_path + @dtsname   
SET @SQLcmd = 'dtsrun /F '+ @dtsname
EXECUTE @error = master..xp_cmdshell @SQLcmd

注：依照我的经验，因为dtsrun是一个dos程序，如果你使用循环来执行多个DTS,似乎应该执行完一个DTS包后，休息5S,等待dtsrun.exe消失再执行下一个，这个问题曾困扰我几乎崩溃，就是程序执行时卡在那里很久也执行不完。后来看任务器里发现有多个dtsrun.exe,估计是资源未释放吧。未深究。改用休息5S后，执行很顺利。任务器里只看得到一个dtsrun.exe。

----

执行bulk insert :

set @SQLcmd = 'BULK INSERT charge_transfer FROM '''+ @thiscycle_file + '''  WITH (FIRSTROW = 2,FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'

exec(@SQLcmd)

*注：如果遇到你设置的表栏位比较多，而txt文本的数据栏位比较少(有些栏位需要计算生成)，则这个显得稍微复杂。需要格式文件，格式如：

8.0
5
1       SQLCHAR       0       20      ","                       4     calling              Chinese_PRC_CS_AS
2       SQLCHAR       0       20      ","                       5     called               Chinese_PRC_CS_AS
3       SQLCHAR       0       20      ","                       6     startdateendtime     ""
4       SQLINT        0       4       ","                       7     duration             ""
5       SQLINT        0       4       "\r\n"                    8     callcost             ""

col1:宿主文件顺序；col2:宿主文件数据类型;col4:宿主文件字段长度;col5,列分隔符;col6:数据库字段顺序(关键在这，它允许跳过某些列);col7:table的列名;col8:table 列的排序规则，char,varchar必须输入，其它为null.

//20070914: modify    如果你使用txt文本，则col2:宿主文件数据类型全部为SQLCHAR,只是长度根据实际文件而定

通常，我们需要评估导入的效率，所以需要在exec(@SQLcmd)前后抓取时间，等执行完后，计算执行时间，这是有必要的；如果出现错误，需要写入日志，因为这些处理是在后台执行，一般是晚上，唯一的处理方式是报告管理员。对于正常的步骤也需要写入日志，否则我们无法看到程序执行的细节步骤了，对管理和调试都没有好处。

5.单一导入功能的procedure和DTS设置好后，你可以建立一个schedule-procedure在job里定时调用(需开SQL agent)，并且要查看调用作业是否成功，要找到中途错误跳出的的问题修改之，否则导入不会继续。当然，我的做法是把DTS的名字。单一的procedure的名字都设置在一个表格里，设置编号，执行先后顺序，执行的时段(一天可能执行多次)，这样看是DTS包还是procedure可调用不同的通用调用语句。以后要增加表时，你只要写好存储器，把存储器名字加入这个表即可，这也是一种框架式的设计思路。

6. 导入数据后，再根据需要计算一些中间结果，产生分析报表等，我接触到的系统，有每天同步两次的表，有旬表，有月表，还有星期表，每天更新后，在原表基础上我还增加了大量的附加栏位，比如分局，机房等，我则通过其它计算得到这两个栏位，这样产生报表时，或查询时才会很快。而旬表，月表都是一些最终结果，一般导入，体积太大则分一下表，汇总一下，或校验有没有异常，核算一些如增量啊之类的。算是不复杂。

7. 在执行一系列DTS,procedure之后，在最后，最好执行备份，数据库收缩，优化等。特别提醒的是，你需要关注tempdb的收缩，因为当你有创建临时表的话，这个数据库会变得很大。收缩主要是解决大量数据删除和插入后膨胀得日志，因为日志会变得难以想象的大。注意，日志需要先截断，然后收缩才行。

我现在接触的系统，基本作到完全自动，备份和数据收缩，优化不用客户的管理员操心。如果要让他们为这个系统操心的话，无疑会增加种种难度，他们短期是难于掌握的。

8.如果遇到数据都是G级的情况，内存最好在4G的样子，两年前我是配2颗xeon,现在都是双核，但我还是建议用户用大内存，虽然他们看到花上万块来买几根条子不理解，但效果是好的。对我而言，导入，汇总，索引，复杂分析，报表等，没有内存支撑简直就无法跑得出来。因为数据条数通常在千万条。而且为了跨速处理，都是大批次的处理。当然如果你在1G内存下去作这么大数据的处理，处理完数据，电脑基本就没反映了。

9.特别提示：如果要清除一张表，比如完全更新的话，则使用truncate table 来清理，可以在瞬间搞定，不然你光删除几个百万级的表就够删一晚上了。

10.特别提醒：如果需要导入大量数据，一般需要在导入前drop掉index，等待数据导入后再创建index，否则边插入边创建索引是没有效率的。