"BULK INSERT ……"批量把txt导入数据表

 

1. 存储过程[sp_BULK_AddNum]

    此存储过程 批量导入 指定的文本文件到数据表中，同时返回批量导入的记录数量

ALTER PROCEDURE [dbo].[sp_BULK_AddNum] -- Add the parameters for the stored procedure here --@Table varchar(1000), --表名 @FileName varchar(1000), --文件名 @Field varchar(10), --字段分割符 @Row varchar(10) --行分割符 AS BEGIN declare @SqlString nvarchar(2000) declare @RowCount int set @SqlString = 'BULK INSERT dbo.NumInfo FROM '''+@FileName+''' WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR ='''+@Field+''', ROWTERMINATOR = '''+@Row+'''); set @RowCount = @@rowcount' EXEC sp_executesql @SqlString, N'@RowCount int out', @Rowcount out --print @rowcnt return @RowCount END

 

2. txt文件模板

    注意：每行数据结尾都用回车换行【/r/n】,特别是最后一行要注意，中间字段用制表符区分【/t】7 1599 车赞 5100 23 上海 上海 41区 汉城路96# 1 1 23.3 1991-1-1 1991-1-1 0 1 0 1 1 1 8 1599 车赞 5100 23 上海 上海 41区 汉城路96# 1 1 23.3 1991-1-1 1991-1-1 0 1 0 1 1 1 9 1599 车赞 5100 23 上海 上海 41区 汉城路96# 1 1 23.3 1991-1-1 1991-1-1 0 1 0 1 1 1

 

3. cs文件

/// <summary> /// 直接用txt文件批量导入号码 /// </summary> /// <param name="FileFullName">文件完整文件名（包括路径）</param> /// <param name="Field">字段分隔符</param> /// <param name="Row">行分隔符</param> /// <returns>批量导入的数量</returns> public int AddNum(string FileFullName, string Field, string Row) { //throw new NotImplementedException(); int iReturn = 0; //创建参数 SqlParameter[] tParms = new SqlParameter[] { new SqlParameter(PARM_BULK_FILENAME, SqlDbType.VarChar, 1000), new SqlParameter(PARM_BULK_FIELD, SqlDbType.VarChar, 10), new SqlParameter(PARM_BULK_ROW, SqlDbType.VarChar, 10), new SqlParameter(PARM_BULK_ROWCOUNT, SqlDbType.Int) }; tParms[3].Direction = ParameterDirection.ReturnValue; //绑定参数值 tParms[0].Value = "" + FileFullName + ""; tParms[1].Value = "" + Field + ""; tParms[2].Value = "" + Row + ""; //执行查询 iReturn = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringNum, CommandType.StoredProcedure, "sp_BULK_AddNum", tParms); return iReturn; }