How to adapt or map data to sql?

Shockerman · Apr 28, 1999

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: 
Code1 Code2 Breed DogID Description 
B1 C2 Collie 43 brown with spots 
 
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. 
 
Code Breed DogID Description 
198 Collie 43 brown with spots 
 
Please let me know if you have any thoughts or ideas where I could find information.

nitram · Apr 30, 1999

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.

Shockerman · Apr 30, 1999

Nitram, 
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? 
 
insert into NEWTABLE (Code) values (198) AND select breed, dogid, description from OLDTABLE where (Code1=B1) AND (Code2=C2); 
 
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 · May 14, 1999

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

Hallsys · May 14, 1999

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: 
 
CREATE VIEW myFinalDogShowView as (Select Code, Breed, DogID, Description FROM oldtable INNER JOIN maptable ON oldtable.code1 = maptable.code1 and oldtable.code2 = maptable.code2) 
 
This presumably gives you a permanently updatable dog categorization library as new ASCII files roll in.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to adapt or map data to sql?

Shockerman

MIS

nitram

Programmer

Shockerman

MIS

nitram

Programmer

Hallsys

Programmer

Similar threads

Part and Inventory Search

Sponsor