首页 > 代码库 > 【51CTO/BBS】求助:累加数据怎么通过存储过程实现自动入库 ...
【51CTO/BBS】求助:累加数据怎么通过存储过程实现自动入库 ...
原帖地址:http://bbs.51cto.com/thread-1133114-1.html
问题描述:
CREATE TABLE [CHR_sgs21] ( [操作索引号] [int] NOT NULL, [呼叫参考号] [bigint] NOT NULL, -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_111542-20140528_111819___(20140521_090544-20140521_100056).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_111819-20140528_111936___(20140521_090209-20140521_100204).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_111936-20140528_112000___(20140521_083339-20140521_100225).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112000-20140528_112120___(20140521_084601-20140521_100342).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112120-20140528_112236___(20140521_080934-20140521_100452).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112236-20140528_112354___(20140521_084126-20140521_100600).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112354-20140528_112509___(20140521_092117-20140521_100710).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112509-20140528_112624___(20140521_085026-20140521_100821).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112624-20140528_112739___(20140521_074949-20140521_100934).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112739-20140528_112854___(20140521_073518-20140521_101044).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_112854-20140528_113009___(20140521_092749-20140521_101230).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘) -- BULK INSERT [CHR_sgs21] FROM ‘E:\CHR_sgs21_20140528_113009-20140528_113124___(20140521_083554-20140521_101425).dat‘ WITH (FORMATFILE = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘)
从以上内容可以看出,还多出了“ --”,还需要把这个删除,让以上内容自动入库,同时,
BULK INSERT,以后的内容还在不断的不定时的递增,不能把以前的已经入库了的再次执行!求脚本,谢谢!
解决方案:
1. 具体逻辑是,循环查询BULKINSERT目录下的DAT文件,对于每一个文件,执行BULK INSERT操作后,将文件MOVE到ARCHIVE目录。
2. 封装成SP后,做成JOB定时执行。
3. 以下脚本未经调试。请楼主自行调试。建议在执行前将@SQL\@CMD变量先PRINT出来。
4. 该问题,可以作为归档方案。建议参考下我的博文:SQL Server 数据归档方案
SET NOCOUNT ON; --定义变量 DECLARE @FullTableName nvarchar(256) -- 完整表名 ,@BulkInsertFolder nvarchar(4000) --数据目录 ,@ArchiveFolder nvarchar(4000) ,@FormatFile nvarchar(256) --格式化文件 ,@cmd nvarchar(4000) ,@sql nvarchar(4000) ,@rtn int ,@FileName nvarchar(4000); -- 临时变量:数据文件名 SELECT @BulkInsertFolder = ‘E:\BulkInsert\‘ ,@ArchiveFolder = ‘E:\Archive\‘ ,@FullTableName = ‘CHR_sgs21‘ ,@FormatFile = ‘D:\CDRFilterTool\FilterTask/V3R7C01SPC100_Do\sgs21.fmt‘ ; -- 接收cmd返回结果 CREATE TABLE #t(s nvarchar(4000)); -- 读取文件 SELECT @cmd = ‘dir /a:-d/b/o:n "‘ + @BulkInsertFolder + ‘_*.dat"‘; INSERT #t(s) EXEC master..xp_cmdshell @cmd; DECLARE @FileCount int; SELECT @FileCount = COUNT(*) FROM #t WHERE s IS NOT NULL; WHILE(@FileCount > 0 ) BEGIN SELECT TOP 1 @FileName = s FROM #t; DELETE #t WHERE s = @FileName; SELECT @FileCount = COUNT(*) FROM #t WHERE s IS NOT NULL; SELECT @sql = N‘BULK INSERT ‘ + @FullTableName + ‘ FROM ‘ + @BulkInsertFolder + @FileName + ‘WITH (FORMATFILE = ‘ + @FormatFile + ‘ )‘; EXEC master.sys.sp_executesql @sql; -- Archive SELECT @cmd = ‘move "‘ + @BulkInsertFolder + @FileName + ‘" ‘ + @ArchiveFolder; -- SELECT @cmd; EXEC @rtn = master..xp_cmdshell @cmd; IF(@@ERROR <> 0 OR @rtn <> 0) BEGIN CONTINUE; END END DROP TABLE #t;
本文出自 “SQL Server Deep Dives” 博客,请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1584942
【51CTO/BBS】求助:累加数据怎么通过存储过程实现自动入库 ...
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。