BCP、Bulk...Insert插入CSV文件：自动跳过增长表列

--产生格式文件

exec master..xp_cmdshell 'bcp  logparse.dbo.pubOriginalData format null -T -c -t , -f e:\csv\data.xml'

 

--手动修改格式文件

 

declare @servername varchar(30)

declare @data varchar(20)

declare @sql Nvarchar(2000)

 

SET IDENTITY_INSERT dbo.pubOriginalData on

DBCC CHECKIDENT ( 'dbo.pubOriginalData', RESEED, 1 )

 

if (DATEPART(hour, getdate())>11)

begin

set @data='_07.csv'

end

else

set @data='_13.csv'

print @data

declare get_name cursor for select servername from dbo.pubServerInfo where logcollectstat='Y'

open get_name

fetch next from get_name into @servername

while @@fetch_status=0

begin

--select @path='e:\csv\'+ @servername+'_'+@data+'.csv'

select @sql=N'BULK INSERT [LogParse].[dbo].[pubOriginalData] FROM '+

            N'''e:\csv\'+ @servername + @data + '''' +

       N' WITH

      (

         KEEPIDENTITY,

         FIELDTERMINATOR ='','',

         ROWTERMINATOR =''\n'',

         FIRSTROW  = 1,

         FORMATFILE = ''e:\csv\data.xml''

      )'

EXEC sp_executesql @sql

fetch next from get_name into @servername

end

close get_name

deallocate get_name

 

 

a1.JPG

a2.JPG

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9932141/viewspace-618305/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9932141/viewspace-618305/