CREATE TABLE img ( [name] VARCHAR(265) ,[image] VARBINARY(MAX) )
CREATE TABLE l ( [imgfilename] VARCHAR(265) )
DECLARE @SQL AS NVARCHAR(2000)
SET @SQL = N'xp_cmdshell ''DIR E:\Images /B'''
INSERT INTO l ( imgfilename ) EXEC sp_executesql @SQL
GO
DECLARE @SQL AS NVARCHAR(2000)
DECLARE @ImgFilename AS VARCHAR(200)
DECLARE cur CURSOR FOR
SELECT imgfilename
FROM l
OPEN cur
FETCH NEXT FROM cur INTO @ImgFilename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @SQL = 'insert into img(name,image)
SELECT
REVERSE(substring(REVERSE(''' + @ImgFilename + ''') ,charindex(''.'',REVERSE(''' + @ImgFilename + ''' ),1)+1,LEN('''
+ @ImgFilename + '''))) ,
(SELECT img.bulkcolumn FROM OPENROWSET(BULK ''E:\Images\' + @ImgFilename + ''',SINGLE_BLOB) AS img)'
EXEC sp_executesql @SQL
FETCH NEXT FROM cur INTO @ImgFilename
END
CLOSE cur
DEALLOCATE cur
Keine Kommentare:
Kommentar veröffentlichen