Duplicating Image Data
-
Not sure exactly how to approach this so any help would be greatly appreciated. I'll try to simplify this a little bit. We have an activity table and users can attach multiple files to each activity. These files are stored in a seperate table in an image column and the two tables are tied together with a common id (acid). We have a user that has attached 19 files to an activity and needs to set up additional activities for other accounts in the database with those same 19 files attached.
Is there anyway I can easily either export or copy those files so they are duplicated and attached to the other activity records? I know there are pointers and such for the image files and basically just need a little direction on this.
Quite honestly I probably could have put these all in with our application by now but at this point really want to figure out how to do it in sql because I have a feeling I may have to do it again in the future.
Thanks in advance for your help.
-
You can do this in SQL -
insert into tblImage(acid, image)
select xxxx, t1.image
from tblImage t1
where t1.acid = yyyy
xxxx is literal text and should be replaced with the new account number (ie for the account that the new files are to be copied to).
yyyy is the account ID for the account already containing the 19 files (ie the account from which the files are to be copied).
This query will create the 19 files just for account xxxx. It needs to be re-run for tho other accounts (amending xxxx - the target account - each time before running).
Hmmm, hope that makes sense
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil. That made a lot of sense and it helped. I think I may have simplified too much though. I'm actually dealing with three tables and now that I have to try and accomplish this, I'm not sure why we did it this way.
Anyhow. I used what you gave me to populate our ac_dochead table and it worked perfect. That table has fields of: docid, acid, filename, description, displayname, last_update. I used the following to populate this table based on another activity with the documents I needed:
INSERT INTO ac_dochead (acid, filename, description, displayname, last_update)
SELECT xxxx, ah1.filename, ah1.description, ah1.displayname, ah1.last_update
FROM ac_dochead ah1
WHERE ah1.acid = yyyy
Now the issues is that the actual documents are in a separate table called ac_docdetail containing the fields of docid and doc. The doc field is the image field.
I've tried a couple of things to try and get the ac_docdetail table poplulated but haven't had any luck.
Any ideas would be helpful. Thanks.
Curt
-
This is by far the most complete discussion on BLOBs and SQL Server I know
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
How is the DocID field (the primary key?) in ac_docdetail generated? Is it an IDENTITY field?
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The DocID field is an Identity field in the ac_dochead table and the system we are using places the DocID along with the blob into the ac_docdetail table once the record has been created in ac_dochead.
Not a great design, I realize, but it's what we have to deal with... for now anyway.
-
OK - may not be a great design, but it's very a common way of doing header/detail relationships.
So you now have several accounts which have got 19 header records but no associated detail records.
For each of these records we need to populate the ac_docdetail table, with DocID being the link between the two. The tough bit!
First step is to populate the docdetail table with the list of the new DocIDs:
Insert into ac_docdetail(docid)
select s.DocID
from ac_dochead s left outer join ac_docdetail d
on s.docid = d.docid
where d.docid is null
Next step is to populate the image field in each of these new records.
Update d
Set image = d2.image
From ac_dochead s inner join ac_docdetail d
On s.docid = d.docid inner join ac_dochead s2
On s.filename = s2.filename inner join acc_docdetail d2
On s2.docid = d2.docid
Where d.image is null
And s2.acid = --insert the acid of the account to copy the image from
This is complex stuff, so I may be a little bit off as I haven’t tested it, but hopefully you can see what I am trying to do: we’ve got to navigate from the ‘empty’ docid to a populated docid (I’ve assumed that this can be done uniquely via the filename field on the ac_dochead table) for the account from which we want to copy. Then we need to get the image data for that docid and copy it across to the new record.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply