SQL Server Performance

Inserting Image file in Table

Discussion in 'SQL Server 2005 General DBA Questions' started by Manoj118, Apr 15, 2008.

  1. Manoj118 New Member

    Hi
    I want to insert images to a table and have some GBs of data (Jpg files) to insert.
    I have few question on this :-
    1 using bult insert command images can be inserted but i have to also insert image file name,
    table structure may be as
    ImageDocumentName (char(100)), ImageOfDocument varbinary(max)
    2. will performance of sql server be slow ?
    Manoj

  2. ghemant Moderator

  3. madhuottapalam New Member

    There are couple of options available. This is one good resource in this regard
    http://www.codeproject.com/KB/database/ImageSaveInDataBase.aspx.
    Else you may try this dynamic sql script. This import images one by one. You may make it more dynamic like the foldername as i/p parameter kindCreate
    Proc spLoadImagesFromLocalFolderas
    declare
    @dir sysname, @cmd Varchar(8000)Declare
    @Fname nvarchar(200),@SQLStmt nvarchar (500)DECLARE
    @ParmDefinition NVARCHAR(500),@cnt int
    select @dir = 'C:'
    If object_id('Tempdb..#Files') IS Not Null
    BeginDrop table #Files
    End
    CREATE TABLE #files ( RowNum int identity, fname varchar(8000) NULL)SET
    @cmd = 'dir ' + QUOTENAME(@dir,'"') + ' /T:W /A:-D /b'INSERT
    INTO #files (fname)EXEC
    master.dbo.xp_cmdshell @cmdprint
    @cmdDELETE
    FROM #files WHERE fname IS NULLDELETE
    FROM #files WHERE fname NOT LIKE '%.JPG'SELECT
    @fname = ''select
    @Cnt=count(*) from #FilesSET
    @ParmDefinition = N'@Fname nvarchar(200)';WHILE
    @Cnt>0BEGIN
    SELECT @fname = fname FROM #files WHERE RowNum > @cntset @SQLStmt='INSERT INTO myTable(FileName, Document) SELECT @Fname AS FileName,
    * FROM OPENROWSET(BULK N'''
    +@Fname+''', SINGLE_BLOB) AS Document'print @SQLStmt exec sp_executesql @SQLstmt,@ParmDefinition, @Fname=@Fname END--DROP TABLE #files
    Please let us know if you have any issue.
    NB : If you have any plan to migrate to sql 2008 i would suggest to checkout Filestream feature of SQL Server 2008. i have blogged it
    http://experiencing-sql-server-2008.blogspot.com/2008_03_01_archive.html
    Madhu
  4. Madhivanan Moderator

Share This Page