Importing text files with Bulk Insert on MS SQL Server 2000

发布时间:2017-5-23 0:00:48 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"Importing text files with Bulk Insert on MS SQL Server 2000",主要涉及到Importing text files with Bulk Insert on MS SQL Server 2000方面的内容,对于Importing text files with Bulk Insert on MS SQL Server 2000感兴趣的同学可以参考一下。

Importing text files Author Nigel Rivett This process will import text files that arrive in a directory into a table. It will process every file in the directory with the correct filemask and move the file to an archive directory on completion. It can be used in conjunction with an ftpget SP to import files from a ftp server (see ftp). /* Create the directories c:/Transfer/Archive/ Create these text files in c:/transfer/ bcp1.txt aaammm0120030101 bbbnnn0220030102 cccooo0320030103 bcp2.txt abcxyz5320030104 defhhh1020030105 cdezzz1120030106 fsajku9920030107 Create the table create table BCPData ( fld1 varchar(20) , fld2 varchar(20) , fld3 int , fld4 datetime ) Now run the import exec ImportFiles 'c:/Transfer/' , 'c:/Transfer/Archive/' , 'bcp*.txt', 'MergeBCPData' You can now move the files back from the archive directory to the transfer directory and import again. If this SP call is scheduled then it will import and archive any files that arrive in the transfer directory with the corect file mask. Enhancements The import should be logged to a table in ImportFiles The filename should have the datetime appended to it when archived if you wish to be able to import files with the same name The MergeData SP should log the number of records imported */ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ImportFiles] GO create procedure ImportFiles @FilePath varchar(1000) = 'c:/Transfer/' , @ArchivePath varchar(1000) = 'c:/Transfer/Archive/' , @FileNameMask varchar(1000) = 'bcp*.txt' , @MergeProc varchar(128) = 'MergeBCPData' AS set nocount on declare @ImportDate datetime select @ImportDate = getdate() declare @FileName varchar(1000) , @File varchar(1000) declare @cmd varchar(2000) create table ##Import (s varchar(8000)) create table #Dir (s varchar(8000)) /*****************************************************************/ -- Import file /*****************************************************************/ select @cmd = 'dir /B ' + @FilePath + @FileNameMask delete #Dir insert #Dir exec master..xp_cmdshell @cmd delete #Dir where s is null or s like '%not found%' while exists (select * from #Dir) begin select @FileName = min(s) from #Dir select @File = @FilePath + @FileName select @cmd = 'bulk insert' select @cmd = @cmd + ' ##Import' select @cmd = @cmd + ' from' select @cmd = @cmd + ' ''' + replace(@File,'"','') + '''' select @cmd = @cmd + ' with (FIELDTERMINATOR=''|''' select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')' truncate table ##Import -- import the data exec (@cmd) -- remove filename just imported delete #Dir where s = @FileName exec @MergeProc -- Archive the file select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName exec master..xp_cmdshell @cmd end drop table ##Import drop table #Dir go if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MergeBCPData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[MergeBCPData] GO create procedure MergeBCPData AS set nocount on -- insert data to production table insert BCPData ( fld1 , fld2 , fld3 , fld4 ) select fld1 = substring(s,1,3) , fld2 = substring(s,4,3) , fld3 = convert(int,substring(s,7,2)) , fld4 = convert(datetime,substring(s,9,8)) from ##Import go

上一篇:访问打包在Jar文件中的图片资源
下一篇:sqlpager改进版,分页的最佳选择,完整源码+中文注释

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

腹肌贴健身器材智能腹部训练健腹器肌