sql - 如何将音频插入 SQL Server

Question

Insert Into [dbo].[Letterland] ([letter],[letterImage])
   Select 'a', BulkColumn,
   From Openrowset (bulk 'H:\Data\D\ll-image\annie.png', Single_blob) as img 

Insert Into [dbo].[Letterland] ([letterDescAudio])
    Select 'a', BulkColumn, BulkColumn
   From Openrowset (bulk 'H:\Data\D\11-image\aa.wav' Single_blob) as img 

Insert Into [dbo].[Letterland] ([letterSound])
    Select 'a', BulkColumn, BulkColumn
   From Openrowset (bulk 'H:\Data\D\ll-image\a.wav', Single_blob) as img 

这是我尝试过的，但我知道这是不正确的。我正在尝试将数据作为单行插入。

[dbo].[Letterland] ([Letter], [letterImage], [letterDescAudio],  [letterSound])

Answer 1

好吧，您需要先将三个 blob 加载到变量中，然后执行单个 INSERT 将它们全部插入到您的表中 - 如下所示:

-- declare a VARBINARY(MAX) variable to hold the "image"
DECLARE @Image VARBINARY(MAX)

-- load the "image"
SELECT @Image = BulkColumn,
FROM Openrowset (bulk 'H:\Data\D\ll-image\annie.png', Single_blob) as img 

-- declare a VARBINARY(MAX) variable to hold the "Desc Audio" and load it
DECLARE @DescAudio VARBINARY(MAX)

SELECT @DescAudio = BulkColumn
FROM Openrowset (bulk 'H:\Data\D\11-image\aa.wav' Single_blob) as img 

-- declare a VARBINARY(MAX) variable to hold the "Sound" and load it
DECLARE @Sound VARBINARY(MAX)

SELECT @Sound = BulkColumn
FROM Openrowset (bulk 'H:\Data\D\ll-image\a.wav', Single_blob) as img 

-- now do the INSERT with all bits ready to go    
INSERT INTO dbo.Letterland (letter, letterImage, letterDescAudio, letterSound)
VALUES ('a', @Image, @DescAudio, @Sound)