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
标签： SQL
