Hi !

In my Database there are around 20 Tables with
Source-Data(ID, Text, .....)
which should be imported to the according
destination-Tables(ID, Text_ID,.....).

The Source Tables contain various Text-Fields. According to simplify
translation, all text-fields should be imported in a table Text(ID)
and a table TextVariante(ID, Text_ID, Language_ID). So every text can
exist in more translations. During the import all equal text shorter
than 255 should only be imported once, so that duplicates are
filtered.

My solution to this problem is:
Writing a selectable Stored Procedure SP_InsertText, which stores the
text if it is longer than 255 directly, or otherwise checks if the
text already exists and store only new ones. The Procedures returns
for every ID of the table, the according ID of the Text-Table, which
contains the text.
Input: TableName, TextColumnName, IDColumnName
Output: ID, Text_ID

Now I should be able to write an import statement like this

INSERT INTO Destination-Table(Text_ID)
SELECT Text_ID
FROM Source-Table
JOIN SP_InsertText('Source-Table','Text','ID')
ON Source-Table.ID = SP_InsertText.ID

I think I have to use Execute Statement in SP, because of the
tableName Parameter. And I would have a SP, that is selectable and
performs Insert-Operation, which is dangerous according to Helen's
Book.

Are there any other negative Aspects?
Is there any other better solution?

Thanks, Mischa