How to adapt or map data to sql?

[Shockerman]

Does anyone have any ideas on how I would go about setting up a sql program or any other type of program that could take a tab delimited ASCII file and be able to add the information to a table based on mapping constraints. For example, the ASCII file would look like this:<br>
Code1 Code2 Breed DogID Description<br>
B1 C2 Collie 43 brown with spots<br>
<br>
The program would need to understand that a dog from (B1, C2) would belong in my category# 198 so my table would look like this.<br>
<br>
Code Breed DogID Description<br>
198 Collie 43 brown with spots<br>
<br>
Please let me know if you have any thoughts or ideas where I could find information.

 

[nitram]

Depends on what dbase you are using but if you didn't want to write programs to parse the data, then I would create an input table with columns matching the inital ascii text file and then write an 'on_insert' trigger (dbase specific syntax) which fired on insert and contained the logic which will perform the data manipulation, probably inserting data into another table.<br>
<br>

 

[Shockerman]

Nitram, <br>
Thank you for your response, I need to read up a bit on triggers. Does you or anyone know if there is a way to do something like this?<br>
<br>
insert into NEWTABLE (Code) values (198) AND select breed, dogid, description from OLDTABLE where (Code1=B1) AND (Code2=C2);<br>
<br>
I'm trying to add a when clause to an insert statement. So, for this example, I wanted to use 198 in the new table with all the same other information from the old table except the codes. Let me know what you think?

 

[nitram]

insert into NEWTABLE<br>
(select 198, breed, dogid, description<br>
from OLDTABLE<br>
where code1='B1' and code2='C2');<br>
<br>
<br>
Should work (I think). If 198, 'B1' and 'C2' are<br>
not constants then you'll need to wrap the whole<br>
lot up in into a procedure and pass these in as<br>
parameters.<br>
<br>
What database server are you using ?

 

[Hallsys]

Why not import the table to the server as is? Then set up one more table that has Code1, Code2, and Code (a map or ownership table) showing the relationship between The other codes and your categorization. Then build your final output view on the server using this SQL:<br>
<br>
CREATE VIEW myFinalDogShowView as (Select Code, Breed, DogID, Description FROM oldtable INNER JOIN maptable ON oldtable.code1 = maptable.code1 and oldtable.code2 = maptable.code2)<br>
<br>
This presumably gives you a permanently updatable dog categorization library as new ASCII files roll in.