September 11, 2008 at 4:35 am
lets say i have a folder with images (xxxxx.tiff)
and thats xxxxx is the ID number of a table in my schema.
How can i put a reference of this file in a field of that table but the reference to be coresponding to the ID?
e.g
Table1
ID / add / ref
123 / cocoon str /
I want to access the table for all the records that doesn't include ref to make them as followes:
123/ cocoon str/ 123.tiff
September 11, 2008 at 5:01 am
Based on what you submitted it looks like you need to do an update similar to the following;
update table1
set ref = 'your reference here'
where id = 123
If you are looking for something more dynamic please phrase your question differently and provide more details.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 11, 2008 at 5:06 am
This is what I am looking But not for only 1 record. for every record that is in the table and folder.
The table might consist of 500 entries each day. and the folder may consist of 500 or less images.
and I want at the end of each day to perform a process that will update the table with the correct references.
Old Table1
ID / ADD / REF
123 / hight str /
555 / low str /
666 / oxford /
777 / london /
999 / westferry /
new Table1
ID / ADD / REF
123 / hight str / 123.tiff
555 / low str / 555.tiff
666 / oxford / 666.tiff
777 / london /
999 / westferry /
etc etc
September 11, 2008 at 5:14 am
You will still have to do an update but something more dynamic. Ultimately you will need a scripting method for reading the contents of a folder. Check out the "Scripting Guys" on the MS website. IF your files are names as you have in the example then you should be pretty well set to match the file with the table id column.
So, once you have the script to read the contents of the folder, you are going to have to use either one of 2 methods to get the data into SQL.
1. execute your updates from within your script that reads the folder
2. insert the contents of the folder into a table and then use t-sql to update the main table with the file information.
I'm sure that you can find the scripts out there that will get you well down the road toward completion on this. Enjoy!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply