Hello,

I am writing an importer, which collects the Data from several
source-tables and imports them to the according destination tables.
My Problem:
All the text Data (VarChar and Blobs) in around 20 source tables should
be imported to one Table (Blob), which stores all the text Data. The
destination table only stores the ID of the Text-Table. Duplicates
should be elimated so that several Destination Table can contain the
same Text-ID.
So I think I need a SP for inserting all the text Data in one table (and
elminitation duplicates) and I need a SP which gives me the correct ID
for a given Text, so that I can store it in my Destination Table.
My solution is one stored procedure with following input:

sourceTableName,
textColumn,
idColumn

and the output:

id,
textID

The Stored Procedure checks for every line in the sourceTable if the
text is already in the textTable. If it is it returns the pair
(sourceID, TextID) if not it inserts the text and returns (sourceID,
newTextID).

So I think I can write following statement for the insert of a source-table:

INSERT INTO DestinationTable(textID)
SELECT SP_InsertText.textID
FROM SourceTable
JOIN SP_InsertText('SourceTableName','TextColumnName','IDColumnName')
ON SourceTable.ID = SP_InsertText.ID

I hope I have made myself clear, because I posted in this group a month
(8.7.2005) ago and nobody answered. So please just give me an indication
if this is a good approach or if I should try something different.

Thanks, Mischa