Insert jpgs and file name into table

Question

Post reply

Insert jpgs and file name into table

slsmithstone

Old Hand

Points: 371
More actions
January 13, 2012 at 6:44 pm

#266438

Hello -

I need to insert about 1600 pictures (jpgs) and their file names into a sql server table. Can anyone tell me how to do this?

Any help will be appreciated!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

Do you have to store the images in the database? If you are not familiar with it FILESTREAM may be a better alternative. Take a look at BOL here. Depending on your usage the performance is likely much better with this type of approach.

If you must store your images in the database you will need to use a program outside of sql to insert your images. For example in .NET you read your image in a byte array and pass that array as your insert parameter. The filename is just a string so you can handle that like any other varchar.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Paul White SSC Guru Points: 150457 More actions · Answer 2

As Sean says, FILESTREAM is an option, as is storing just the path in SQL Server and keeping the images themselves in the file system. Storing images in the database is another option (particularly if they are around 256KB each or less). If you choose this option, there is a way to add image data directly in T-SQL:



CREATE TABLE dbo.ImageTest

(

    image_id    integer IDENTITY PRIMARY KEY,

    file_path   nvarchar(260),

    name        nvarchar(30),

    picture     varbinary(max) NOT NULL

)

GO

INSERT dbo.ImageTest

    (file_path, name, picture)

SELECT

    N'C:\Users\Paul White\Pictures',

    N'asterix.jpg',

    stream.picture

FROM OPENROWSET

(

    BULK N'C:\Users\Paul White\Pictures\asterix.jpg',

    SINGLE_BLOB

) AS stream (picture);

GO

SELECT * FROM dbo.ImageTest AS it

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 3

If you are looking for a way to also read the contents of a file directory from SQL Server, you could use xp_cmdshell. Another way is to use a SQL CLR function or procedure, see List Directory Contents[/url] or use a pre-built library like the one here: http://nclsqlclrfile.codeplex.com/

This demonstrates loading all the jpeg image files in a directory:



-- Find the files

DECLARE @Files TABLE (name nvarchar(260) UNIQUE)

INSERT @Files (name)

    EXECUTE master..xp_cmdshell

        -- Change this

        'dir /A-D /B /ON "C:\Users\Paul White\Pictures"'

-- Load the image data into the database

DECLARE

    @directory nvarchar(260),

    @name nvarchar(260),

    @sql nvarchar(max);

-- Change this

SET @directory =

    N'C:\Users\Paul White\Pictures';

DECLARE cur_Files

    INSENSITIVE CURSOR FOR

        SELECT f.name

        FROM @Files AS f

        WHERE f.name IS NOT NULL

    FOR READ ONLY;

OPEN cur_Files;

SET NOCOUNT ON;

-- Load each image using dynamic SQL

WHILE (1=1)

BEGIN

    FETCH NEXT FROM cur_Files INTO @name;

    IF @@FETCH_STATUS = -1 BREAK;

    SET @sql =

        N'

        INSERT dbo.ImageTest

            (file_path, name, picture)

        SELECT

            N''' + @directory +  ''',

            N''' + @name + ''',

            stream.picture

        FROM OPENROWSET

            (

                BULK N''' + @directory + '\' + @name + N''',

                SINGLE_BLOB

            ) AS stream (picture);

        ';

    -- Show progress

    RAISERROR ('Loading %s...', 0, 1, @name) WITH NOWAIT;

    EXECUTE (@sql);

END;

CLOSE cur_Files; DEALLOCATE cur_Files;