Different date type table combination and Bulk import data

create procedure [dbo].[Table_Combination]
 @ORG_Order_Table varchar(128),
 @OBJ_Order_Table varchar(128)
as
declare @obj_column_name varchar(128)
declare @obj_column_type varchar(128)
declare @obj_column_length int

declare @org_column_type varchar(128)
declare @org_column_length int

declare @COM_ORDER_TABLE varchar()
set @COM_ORDER_TABLE =@ORG_Order_Table+'_ALL'

declare @sql varchar(4000)
set @sql = 'select * into '+@COM_ORDER_TABLE +' from '+@ORG_Order_Table
exec(@sql)
set @sql='alter table '+@COM_ORDER_TABLE +'
add constraint PK_'+@COM_ORDER_TABLE +' primary key CLUSTERED(委託者コード,注文番号)'
exec(@sql)

set @sql = 'insert into '+@COM_ORDER_TABLE +' select '
declare obj_cur cursor
for
select a.name as column_name,b.name as type_name,a.Max_Length
from sys.columns a
inner join sys.types b
on a.system_type_id=b.system_type_id
inner join sys.objects c
on a.object_id = c.object_id
where c.name=@OBJ_Order_Table
order by a.column_id

open obj_cur
fetch next from obj_cur
into  @obj_column_name,@obj_column_type,@obj_column_length

while @@fetch_status = 0
begin
 select @org_column_type = b.name,@org_column=a.Max_Length
 from sys.columns a
 inner join sys.types b
 on a.system_type_id=b.system_type_id
 inner join sys.objects c
 on a.object_id=c.object_id
 where c.name=@ORG_Order_Table and a.name=@obj_column_name
 if @org_column_type<>@obj_column_type or @org_column_length
 begin
  if(@org_column_type='char' or @org_column_type='varchar' or @org_column_type='nvarchar' )
  begin
   set @sql = @sql+'cast('+@obj_column_name+' as '+@org_column_type+'('+case(@org_column_length) as varchar
(5))+')),'
  end
  else if (@org_column_type='datetime')
  begin
   set @sql = @sql+'case '
   set @sql = @sql+'when '+@obj_column_name+' is null then null '
   set @sql = @sql+'when '+@obj_column_name+'='''' then null '
   set @sql = @sql+'cast(left('+@obj_column_name+',23) as '+@ort_column_type+')'
   set @sql = ' end,'
  end
  else
  begin
   set @sql = @sql+'cast('+@obj_column_name+' as '+@org_column_type+'),'
  end
 end
 fetch next from obj_cur
 into  @obj_column_name,@obj_column_type,@obj_column_length
end
set @sql=left(@sql,len(@sql)-1)+'from '+ @OBJ_Order_Table +' a
where not exists (select 委託コード,注文番号　from '+@COM_ORDER_TABLE +'
where 委託コード=a.委託コード and 注文番号=a注文番号.'



create proc [dbo].[DataImport]
 @tableName varchar(128),
 @filePath varchar(500),
 @pattern varchar(100)
as
set quoted_identifier off
declare @query varchar(1000)
declare @maxl int
declare @countl int
declare @fileName varchar(100)
set @countl = 0
create table #x (name varchar(200))
set @query = 'master.dbo.xp_cmdshell "dir '+@filePath+@pattern+ ' /b"'
print @query
insert #x exec (@query)
delete from #x where name is null
select identity(int,1,1) as ID,name into #y from #x
drop table #x
set @maxl = (select max(ID) from #y)
print @maxl
print @countl
while @countl <=@maxl
begin
 set @countl=@countl+1
 set @filename = (select name from #y where [id] = @countl)
 set @query = 'bulk insert '+@tableName+' from "'+@filePath+@filename+'" with(fieldterminator='','',rowterminator=''\n'')'
 print @query
 exec (@query)
end
drop table #y