csv导入sqlsever脚本

use icbc_data_db

-- 测试sqlcmd
--/*
--sqlcmd -S localhost -U sa -P 123 -i transdata_BatchImport_folder.sql -v vFilePath="E:\20150701.txt" vDateType="1" vImpDate="2015-07-01" -o log.txt
--sqlcmd -S localhost -U sa -P 123 -i transdata_BatchImport_folder.sql -v vFilePath="E:\20150927.txt" vDateType="0" vImpDate="2015-09-27" -o log.txt
--*/

--/*
--sqlcmd -S localhost -U sa -P 123 -i transdata_BatchImport_folder.sql -v vFilePath="E:\Testsql\20150927.txt" vDateType="0" vImpDate="2015-09-27" -o log.txt
--*/

-- sqlcmd -v 参数
-- 文件位置（包含文件名）
-- $(vFilePath)
-- 导入数据类型（0 接听明细表Ans/ 1 交易清单表Trans）
-- $(vDateType) 目前为0/1
-- 导入日期，日期格式必须为yyyy-MM-dd
-- $(vImpDate)
-- bcp分割符
-- $(vBcpT)
-- bcp行结束符
-- $(vBcpR)
-- 从第几行开始
-- $(vBcpF)
-- 到第几行结束
-- $(vBcpL)
declare @FilePath nvarchar(max), @DateType nvarchar(1), @ImpDate nvarchar(10);
-- bcp参数变量
declare @BcpT nvarchar(10), @BcpR nvarchar(10), @BcpF nvarchar(10), @BcpL nvarchar(10);
-- 表名变量
declare @ImpTabName nvarchar(100), @TmpTabName nvarchar(100);
-- sql语句变量
declare @ImpSQL nvarchar(2000), @TmpTransSQL nvarchar(2000);

set @FilePath = N'$(vFilePath)'
set @DateType = LEFT(N'$(vDateType)', 1)
set @ImpDate = CAST(CONVERT(DATE, LEFT(N'$(vImpDate)', 10), 126) as nvarchar(10));

-- 导入tmp表，然后进行数据出错处理，在导入真实数据库

-- 通过$(vDateType)和$(vImpDate)判断出导入数据库表
if @DateType = N'0'
begin
	print N'Set AnsTable';
	set @ImpTabName = N'[icbc_data_db].[dbo].[Ans' + RIGHT(LEFT(@ImpDate, 7), 2) + N']';
	
	-- //////////接听明细表导入参数/////////////
	
	-- 列分割符
	set @BcpT = N'"|"'
	-- 行分割符
	set @BcpR = N'0x0a'
	-- 起始行（接听明细无表头，从第1行开始，共9列）
	set @BcpF = N'1'
	-- 临时表表名（注意一定要包含[]，并指明表路径）
	set @TmpTabName = N'[icbc_data_db].[dbo].[ImpTmp0]';
	
	-- /////////////////////////////////////////
	
end
else if @DateType = N'1'
begin
	print N'Set Trans Table';
	set @ImpTabName = N'[icbc_data_db].[dbo].[Trans' + RIGHT(LEFT(@ImpDate, 7), 2) + N']';
	
	-- /////////交易明细表导入参数//////////////
	
	-- 列分割符
	set @BcpT = N','
	-- 行分割符
	set @BcpR = N'0x0a'
	-- 起始行（交易表有表头，从第2行开始，共10列）
	set @BcpF = N'2'
	-- 临时表表名（注意一定要包含[]，并指明表路径）
	set @TmpTabName = N'[icbc_data_db].[dbo].[ImpTmp1]';
	
	-- /////////////////////////////////////////
	
end
else
begin
	print N'error: wrong vDateType parameter, exit script';
	return;
end

-- 清空临时表
declare @TmpRes int;
declare @tmpSQL nvarchar(2000);
set @tmpSQL = N'delete ' + @TmpTabName;
print N'clear import tmp table';
EXEC sp_executesql @tmpSQL;

-- 导入到临时表
set @ImpSQL =  N'bcp '+ @TmpTabName + N' in ' + @FilePath + N' -c -t' + @BcpT + N' -r' + @BcpR + N' -T -F' + @BcpF;
print @ImpSQL;
EXEC icbc_report_db..xp_cmdshell @ImpSQL;

declare @secondColName nvarchar(50);
set @secondColName = N'null';
-- 获取导入表第二列列名（以免查找真实表已存在当日数据时返回空）
-- 语句例子：select name from syscolumns where id=object_id(N'dbo.Trans01') and colorder = 2
set @tmpSQL = N'set @secondColName = (select distinct max(name) from sys.syscolumns where id=object_id(N''' + @ImpTabName + ''') and colorder = 2)'
EXEC sp_executesql @tmpSQL, N'@secondColName nvarchar(50) output', @secondColName output;
if @secondColName = N'null'
begin
	print N'error: cannot find secondColName, exit script'
	return;
end

-- 将临时表中非指定日期的记录删除
set @tmpSQL = N'delete ' + @TmpTabName + N' where left(Col0, 10) <> ''' + @ImpDate + N'''';
print @tmpSQL;
EXEC sp_executesql @tmpSQL;

-- 导入时，发现真实表已存在当日数据，放弃导入
-- 【之前日期字段为varchar，现在改为date或者datetime，故比较方式要改】
set @TmpRes = 0;
--set @tmpSQL = N'if exists (select 1 from ' + @ImpTabName + N' where left(' + @secondColName + ', 10) = ''' + left(@ImpDate, 10) + N''') select @TmpRes = -1;';
set @tmpSQL = N'if exists (select 1 from ' + @ImpTabName + N' where cast(' + @secondColName + N' as date) = cast(''' + left(@ImpDate, 10) + ''' as date) ) select @TmpRes = -1;';

EXEC sp_executesql @tmpSQL, N'@TmpRes int output', @TmpRes output;

if @TmpRes = -1
begin
print N'date already existed, exit script';
return;
end

-- 导入时，若不是指定时间的数据，删除。仅在临时表中保留指定时间的数据
set @tmpSQL = N'if exists (select 1 from ' + @TmpTabName + N' where left(Col0, 10) <> ''' + left(@ImpDate, 10) + N''') delete ' + @TmpTabName + N' where left(Col0, 10) <> ''' + left(@ImpDate, 10) + N'''';
print N'delete tmpdate which is not @ImpDate';
EXEC sp_executesql @tmpSQL;

-- 接听明细表
-- 【之前日期字段为varchar，现在插入时要cast】
if @DateType = N'0'
begin
	print N'insert into Ans Table';
	--set @tmpSQL = N'insert into ' + @ImpTabName + N'(AnsDatetime,TelNo,Area,CardNo,RiskLevel,TransDesc,AnsDurTime,FuncGrp,TellerId) select Col0,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 from ' + @TmpTabName;
	set @tmpSQL = N'insert into ' + @ImpTabName + N'(AnsDatetime,TelNo,Area,CardNo,RiskLevel,TransDesc,AnsDurTime,FuncGrp,TellerId) select cast(Col0 as dateime),Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 from ' + @TmpTabName;

	EXEC sp_executesql @tmpSQL;	
end
-- 交易明细表
else if @DateType = N'1'
-- 【之前日期字段为varchar，现在插入时要cast】
begin
	print N'insert into Trans Table';
	-- 将Col7中 手机前的"-" 替换
	-- 将Col5中NULL设置为空，将空格设置为空
	set @tmpSQL = N'insert into ' + @ImpTabName + N'(TransDate,TransTime,CardNo,TransDesc,TellerId,TransNo,CardAreaNo,CsTelNo,BeforeInfo,AfterInfo) select cast(Col0 as date),cast(Col1 as time),Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9 from ' + @TmpTabName;
	EXEC sp_executesql @tmpSQL;
end