January 8, 2013 at 10:56 pm
Dear All
I am working in University Project.I have 10000 students physical photos in one directory in corresponding register number file name.My requirement is i want upload all the photos in sqlserver database without using front end.I want details like file name should be store separate column and image store in binary format.
sno registerno Image
January 9, 2013 at 7:58 am
vs.satheesh (1/8/2013)
Dear AllI am working in University Project.I have 10000 students physical photos in one directory in corresponding register number file name.My requirement is i want upload all the photos in sqlserver database without using front end.I want details like file name should be store separate column and image store in binary format.
sno registerno Image
You question has nowhere near enough details for anybody to provide much assistance. If you are really need to store the images in the database I would highly recommend NOT storing them in a table with other information. I would make a table for nothing but those images and a foreign key.
_______________________________________________________________
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/
January 9, 2013 at 8:15 am
don't arbitrarily remove an application or code from doing the work for you; that is what they are designed to do.
the code to upload the images/blobs in a programming language(the front end you seem to want to arbitrarily avoid) is very compact and easy to understand.
a pure TSQL solution will be huge, involve a cursor calling xp_commandshell and will either require the files exist on the server or in a network location that the SQL service has access to , and that is an extra level of headaches.
A CLR solution like http://nclsqlclrfile.codeplex.com would work here as well, but would encounter the same access issues (file on server or a network share that the service has access to)
google can point you to some programming code in the language of your choice to do the work for you in about 20 lines of code total.
Lowell
January 14, 2013 at 2:55 pm
Here is one approach for converting images into table columns with SQL.
This assumes that:
- you have a table with an identification that can be tied to the image file name, and
- the table has a BLOB column to store the file binary in, and
- this is just a data conversion process (I don't think I'd use this in production).
Hope it helps.
/*
Import file binary into Columns in SQL tables
The Process:
Creates a sample student temp table
Builds a cursor set of all student records
Iterates the cursor
Building dynamic SQL to load the image into the student record, and
Executing the dynamic SQL
Cleans-up the cursor
Removes the temp table
*/
/* Example student table... replace the table/column names below */
Create TABLE #StudentTable (StudentID Int, FileImage image)
Insert Into #StudentTable
Select 3708,NULL UNION
Select 3709,NULL UNION
Select 3710,NULL
/* now setup the necessary variables and create the cursor. */
Declare @StudentID int /* the identifier of the record to be updated with the bin */
Declare @FileImagePath nVarChar(255) = 'C:\Temp\' /* the location of the source files (SQL must have access) */
Declare @RunMe nVarChar(Max) /* the dynamic SQL to load the image file */
DECLARE workCursor CURSOR FOR
Select
StudentID,
@FileImagePath + 'IMG_' + Cast(StudentID as nVarChar(128)) + '.JPG' as FileName /* you will need to change this to your file name pattern */
From #StudentTable
Where FileImage is null /* remove this if overwriting pictures is ok */
Order By StudentID
/* open (run the query) the cursor and get the first row's contents into working variables */
OPEN workCursor
FETCH NEXT FROM workCursor INTO @StudentID, @FileImagePath
WHILE @@FETCH_STATUS = 0
Begin
Set @RunMe = '
Update #StudentTable
Set FileImage = (
Select * From openrowset(BULK '''+ @FileImagePath + ''', SINGLE_BLOB) ToLoad
)
where StudentID = ' + Cast(@StudentID as nVarChar (10))
Exec (@RunMe)
FETCH NEXT FROM workCursor INTO @StudentID, @FileImagePath
End
/* clean up the cursor after use */
CLOSE workCursor
DEALLOCATE workCursor
Select * From #StudentTable
Drop TABLE #StudentTable
Steve Pirazzi
ONEWARE, Inc. http://www.oneware.com
Steve Pirazzi
ONEWARE, Inc.
http://www.ONEWARE.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply