J'essaie d'insert plusieurs images dans une colonne de type de données Image
dans sql server 2012
.
Le dossier contient plusieurs files .jpg
et .png
.
J'utilise actuellement ce sql pour insert
un par un
Comment puis-je insert plusieurs images ou le dossier entier merci.
INSERT INTO Images(ImageId, ItemId, Caption, Image) Values(NEWID(), null, null,( SELECT * FROM OPENROWSET( BULK 'C:\Images\image004.jpg', SINGLE_BLOB) AS x))
Voici la table avec l'index cluster sur la colonne 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
En train d'essayer cette approche
--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(subssortingng(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
Cela montre une façon d'importer plusieurs files où les noms de files peuvent être calculés:
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;
Pas joli, mais s'il s'agit d'une import ponctuelle, cela n'a pas d'importance.