July 23, 2004 at 10:29 am
Hello,
I have just jumped headfirst into the world of SQL this week, and am looking for some help updating information in one of my tables.
We are running MS SQL 2000 with the iCode Everest business software, and the eCommerce addon. I currently have about 3000 part numbers where I need to attached images that will show on our website. The only way to do this through Everest is to open the item profile for each part number, and manually browse and select the image files for that item (4 different image files per item). As you can imagine this would take A LOT of time to complete, and we are under tight deadlines to get our site up and running.
The only other option that occured to me was to create an Excel file with the item code , and the four image file fields, and then import them into the SQL table that this information is pulled from. Unfortunately when I attempt this, I receive an error stating (in lamens terms) that the item code is already listed in the table and duplicate entries cannot be added. What I need to do is just update each item code in the table to have the image file names listed under their respective headers, without overwriting any other information in the item code line.
If anyone has any advice for me, or could point me in the right direction, it would be greatly appreciated.
Thank you in advance.
July 23, 2004 at 1:43 pm
I am sure there are several ways to do this.
How about create a new table with the excel file that you created, and then update the image filename columns by joining Item code line to this new table.
import data from excel file to the new table say "itemImageFileName"
update itemCodeLine
set imageFilename1 = B.imageFilename1 , imageFilename2 = B.imageFilename2,...
from itemCodeLine A inner join itemImageFileName B
where A.itemPartNumber = B.itemPartNumber
July 24, 2004 at 10:06 pm
Hi,
Generally if you have table type questions it is easier to get help if you give a little example of the table structure, it helps the geeks see what you need. I am going to make a guess at what you are doing.
First you said you were going to make an Excel spreadsheet of the Item and the imagedata.
then you said it was crashing on the insert because of the duplicate record.
I am guessing that this is your excel table
| Part ID | Images |
| 1 | image Data1 |
| 1 | image Data2 |
| 2 | image Data 3 |
| 2 | image Data 4 |
The problem on your insert is that you are making a table just like this in SQL and the Part ID column cannot be the IDentity collumn because of the repeated ID numbers.
You need to normalize your data to Either 2 or three tables Depending on if the images are on to many with the parts or if the images are Many to Many with the parts.
If an Image Can only have One Part as its Parent, then The Image table can have a column for the part number in it. Like this:
| Part ID | Part Information |
| 1 | 1/4 inch hex cap screw |
| 2 | 1/2 inch hex cap screw |
| 3 | 3/4 inch hex cap screw |
| 4 | 5/8 inch hex cap screw |
Then You Need the images table with a foreign key relating to the above table like this
| Image ID | Part ID | Image Data |
| 1 | 1 | Image Data 1 |
| 2 | 1 | Image Data 2 |
| 3 | 2 | Image Data 3 |
| 4 | 2 | Image Data 4 |
If However, You have one image that can be used for several parts, Then You have to have 3 Tables one for the Pictures, One for images, and one for the Join of the 2 tables. Like These:
| Part ID | Part Information |
| 1 | 1/4 inch hex cap screw |
| 2 | 1/2 inch hex cap screw |
| 3 | 3/4 inch hex cap screw |
| 4 | 5/8 inch hex cap screw |
| Image ID | Image Data |
| 1 | Image Data 1 |
| 2 | Image Data 2 |
| 3 | Image Data 3 |
| 4 | Image Data 4 |
| PartImageID | Part ID | Image ID |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 2 | 4 |
If I have guessed correctly then I would suggest that you actually export your data into different excell tables before the import. otherwise you will need to do the inserts seperately and do a select distimct for the part numbers.
Sorry for the long winded answer but I think you will find this helps a bit. Also I would Suggest "SQL for Smarties" by CELKO it has a lot of design ideas and he is very active in the google groups.
Hope that helps
Tal McMahon
July 26, 2004 at 9:52 am
Sorry, I should have included an example of the table I am working with.
| Active | Itemno | Descript | imageactual | imagethumb | imageicon | imagesmall |
| T | EXAMPLE1 | EXAMPLE SKU 1 | actual-example1.gif | 100-example1.gif | 50-example1.gif | 50-example1.gif |
| T | EXAMPLE2 | EXAMPLE SKU 2 | actual-example2.gif | 100-example2.gif | 50-example2.gif | 50-example2.gif |
| T | EXAMPLE3 | EXAMPLE SKU 3 | actual-example3.gif | 100-example3.gif | 50-example3.gif | 50-example3.gif |
| T | EXAMPLE4 | EXAMPLE SKU 4 | actual-example4.gif | 100-example4.gif | 50-example4.gif | 50-example4.gif |
| T | EXAMPLE5 | EXAMPLE SKU 5 | actual-example5.gif | 100-example5.gif | 50-example5.gif | 50-example5.gif |
This is an example of both the sql table and the excel file I created to import the data I need. I wanted the Excel file to match the table as close as possible. Of course the sql table does not have the actual image file names in it.. yet.
What I was hoping would happen during the import was the "itemno" fields match up, and then sql would import the image filenames into their respective columns in the table. Basically the same as a VLOOKUP in MS Excel. The only information that needs to be imported into the table is the image filenames, unfortunately when I attempted the import I believe it was trying to import the itemno column as well.
As you can see from the example table, there are three different image file sizes per itemno, and each itemno has it's own set of images, so I don't believe creating three seperate tables would help me out. Also the sql table in question has much more information in it than what is showing in my example, such as costs, stock information, sales history, etc... All this of course would have to remain untouched during any import I am able to do.
I appreciate everyones help, and the longer winded the answers the better so no need to apologize!
August 13, 2004 at 4:09 pm
Does anyone have any other ideas?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply