将多个图像插入到sql server中的Image列中
将多个图像插入到sql server中的Image列中
提问于 2013-05-30 04:30:29
我正在尝试将多个图像插入到sql server 2012中的Image数据类型列中。
该文件夹有多个.jpg和.png文件。
我现在正在使用这个sql逐个执行insert。
如何插入多张图片或整个文件夹，谢谢。
INSERT INTO Images(ImageId, ItemId, Caption, Image)
Values(NEWID(), null, null,(
SELECT * FROM OPENROWSET(
BULK 'C:\Images\image004.jpg',
SINGLE_BLOB) AS x))以下是对ClusterKey列具有聚集索引的表
CREATE TABLE [dbo].[Images](
[ImageId] [uniqueidentifier] NOT NULL,
[ItemId] [uniqueidentifier] NULL,
[Caption] [nvarchar](100) NULL,
[Image] [image] NOT NULL,
[ClusteredKey] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Images] PRIMARY KEY NONCLUSTERED
(
[ImageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO目前正在尝试这种方法
--Table to process the files
CREATE TABLE imagelist
(
imgfilename VARCHAR(200)
)
GO
--Put all file name in a table for easy processing
DECLARE @SQL AS NVARCHAR(2000)
SET @SQL =N'xp_cmdshell ''dir c:\Images /B'''
INSERT INTO imagelist (imgfilename) EXEC sp_executesql @SQL
GO
--Import data into target table
DECLARE @SQL AS NVARCHAR(2000)
DECLARE @ImgFilename AS VARCHAR(200)
DECLARE filelist CURSOR FOR
SELECT imgfilename
FROM imagelist
OPEN filelist
FETCH next FROM filelist INTO @ImgFilename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @SQL = 'INSERT INTO Images(ImageId, Image) Values(NEWID(),( select reverse(substring(REVERSE('''
+ @ImgFilename
+ ''') ,charindex(''.'',REVERSE('''
+ @ImgFilename + ''' ),1)+1,LEN('''
+ @ImgFilename
+ '''))) , (SELECT img.bulkcolumn FROM OPENROWSET(BULK ''c:\Images\'
+ @ImgFilename + ''',SINGLE_BLOB) AS img))'
EXEC Sp_executesql
@SQL
FETCH next FROM filelist INTO @ImgFilename
END
CLOSE filelist
DEALLOCATE filelist 回答 1
Stack Overflow用户
回答已采纳
发布于 2013-05-30 07:44:09
这显示了一种导入多个文件的方法，在这些文件中可以计算文件名：
declare @Count as Int = 3622;
declare @Filename as VarChar(128);
declare @Statement as VarChar(256)
while @Count <= 3625
begin
set @Filename = 'C:\Photos\IMG_' + Right( '000' + Cast( @Count as VarChar(5) ), 4 ) + '.JPG';
set @Statement = 'select * from OpenRowSet( Bulk ''' + @Filename + ''', Single_Blob ) as Nought';
execute( @Statement )
set @Count = @Count + 1;
end;不是很好，但如果这是一次性导入，那么它可能无关紧要。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16823259
复制相关文章
相似问题
腾讯云开发者
