数据导入sql数据库.txt

把数据格式为txt或者XLS的文件直接导入SQL数据库

CREATE      PROCEDURE dbo.Sp_DataInput
@Database varchar(1000),--数据库名
@File varchar(1000),--数据库
@Tablename  varchar(1000), --表名
@Filename varchar(1000),-- 待统计的excel文件名
@servername varchar(50) = '127.0.0.1',
@uid varchar(50) = 'sa',
@pwd varchar(50) = 'Tercel',
@XlsTable varchar(50) = 'sheet1$'
AS
declare @filepath varchar(200)
set @filepath='E:\'+'My Documents\'+@File+'\'
declare @execstr varchar(1000)
declare @DataPrint varchar(8000)
declare @trun varchar(100)
set @execstr = 'insert into ['+@Database+'].dbo.['+@Tablename+'] select * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +@Filename +'";User ID=;Password=;Extended properties=Excel 5.0'')...[sheet1$]'
print @execstr
set @trun='truncate table ['+@Database+'].dbo.['+@Tablename+']'
exec (@trun)
exec (@execstr)

GO