Montag, 27. August 2012

Wie lade ich Bilder in eine Tabelle im SQL Server? Und das nur mit SQL?

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: