SQL Server 从表中读取 csv 二进制文件

我目前将 csv 格式的文件存储在磁盘上，然后像这样查询它们：

SELECT *
FROM OPENROWSET(BULK 'C:\myfile.csv',
    FORMATFILE = 'C\format.fmt',
    FIRSTROW = 2) AS rs

其中 format.fmt 是 csv 文件中列的定义格式。这非常有效。 但我有兴趣将文件存储在 SQL Server 表中，而不是将它们存储在磁盘上。 因此，当具有 VARBINARY(MAX) 数据类型列时。我如何查询它们？

如果我有一个像这样的表：

CREATE TABLE FileTable
(
    [FileName] NVARCHAR(256)
    ,[File] VARBINARY(MAX)
)

与一排'myfile.csv', '0x427574696B3B44616....'

例如，如何将该文件内容读入临时表？

如果您确实需要使用 varbinary 数据，可以将其转换回 nvarchar：

DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5468697320697320612074657374

SELECT CAST(@bin as VARCHAR(MAX))
-- gives This is a test

将其转换为该格式后，您可以使用 split 函数将其转换为表格。不要问我为什么 SQL Server 中没有内置的 split 函数，因为它是一个非常明显的疏忽，但事实并非如此。因此，使用以下代码创建您自己的：

CREATE FUNCTION [dbo].[fn_splitDelimitedToTable] ( @delimiter varchar(3), @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ([String] VARCHAR(100), [Hierarchy] int )
AS
BEGIN

    DECLARE @String    VARCHAR(100)
    DECLARE @row int = 0

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @row = @row + 1
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@delimiter, @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String], [Hierarchy] )
        VALUES ( @String, @row )
    END

    RETURN
END

把它们放在一起：

select CAST('one,two,three' as VARBINARY)
-- gives 0x6F6E652C74776F2C7468726565

DECLARE @bin VARBINARY(MAX)
SET @bin = 0x6F6E652C74776F2C7468726565

select * from fn_splitDelimitedToTable(',', CAST(@bin as VARCHAR(MAX)))

给出这个结果：

string hierarchy
================
one    1
two    2
three  3

当然，如果您愿意，您可以将结果放入临时表中以供使用：

select * into #myTempTable
from fn_splitDelimitedToTable(',', CAST(@bin as VARCHAR(MAX)))