文件表文件流列拆分为新表 [英] filetable file stream column split to new table

问题描述

我在sql 2012 DB中有一个文件表，它在file_stream列中保存文本文件数据。

我需要将文本文档中的文本拆分到新表中的不同列。

文本文件中的列由制表符和行按新行字符分隔。

请建议合适的方法。



我们可以使用批量复印吗？



紧急请



i尝试

http://www.sqlservercentral .com /论坛/ Topic1476134-3077-2.aspx＃bm1476786 [ ^ ]



它根据分隔符新行将我的文本文件拆分成不同的行



现在这个结果的每一行应根据标签分成不同的列



请帮助

i have a file table in sql 2012 DB which holds text file data in file_stream column.
I need to split the text in the text document to different columns in a new table.
Columns in the text file is delimitted by tabs and rows by new line character.
Please advice the suitable method.

Can we use bulk copy?

urgent please

i tried
http://www.sqlservercentral.com/Forums/Topic1476134-3077-2.aspx#bm1476786[^]

it is spliting my text file into different rows based on the delimiter new line

now each row of this result should be splitted to different columns based on tab

please help

推荐答案

DECLARE @NextPos INT,       @LastPos INT ,@colcount int=0,@sql varchar(1000)
    truncate table Splitresult

    SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1),
        @LastPos = 0

    WHILE @NextPos > 0
        BEGIN
            IF exists( select  1 from Splitresult where id=@id)
            begin
                set @colcount=@colcount+1
                set @sql='update Splitresultset column'+ltrim(rtrim(str(@colcount)))+'=''' + SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1) +''' where id=' +  ltrim(rtrim(str(@id)))
                EXEC( @sql)
            end
            else
            begin
                set @colcount=@colcount+1
                set @sql='insert into Splitresult(id,column'+ltrim(rtrim(str(@colcount)))+')  select ' +  ltrim(rtrim(str(@id)))+',''' + SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)+''''
                EXEC (@sql)
            end


            SELECT  @LastPos = @NextPos,
                @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
        END

    IF @NextPos <= @LastPos
        IF exists( select  1 from Splitresultwhere id=@id)
        begin
                set @colcount=@colcount+1
                set @sql='update Splitresultset column'+ltrim(rtrim(str(@colcount)))+'=''' + SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos) +''' where id=' +  ltrim(rtrim(str(@id)))
                EXEC( @sql)
        End
        else
        Begin
                set @colcount=@colcount+1
                set @sql='insert into Splitresult(id,column'+ltrim(rtrim(str(@colcount)))+')  select ' +  ltrim(rtrim(str(@id)))+',''' + SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos) +''''
                EXEC (@sql)
        end

这篇关于文件表文件流列拆分为新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！