I have a table and the values need to be inserted from text file. The table is like this. Ext_id should be the first column.
Ext_id Group_id Claim_Number 123 4375 12300
The ext_id always have to be derived from claim_number by stripping off last two digits.
I have the text file with values to be loaded except for Ext_id. How do I derive from Claim_Number and insert/import into table? Anyway can I do it using INSERT query. I dont want to update the table once its inserted becasue i have to do this periodically.
.Layout Claim; /* Provide layout name and */ .Field Group_Id * Char(100); /* define the fields */ .Field Claim_NumberNo * Char(100); /* the claim record. */
.DML Label Inserts; /* This dml request */ Insert into History (Ext_Id , Group_Id , Claim_Number) Values ( SubStr(:Claim_Number,1,Chars(Trim(:Claim_Number))-2) , :Group_Id , :Claim_Number ) ; /* Sets up field 1 as field 3 without the last 2 characters */
.Import Infile INPUT /* Identify import file */ Format Vartext ',' /* csv type file */ Layout Claim /* and record layout */ Apply Inserts;
.End Mload; /* Initiate MLOAD processing */
.Logoff; /* Logoff Teradata Database */
Sorry the spacing gets lost! Maybe use the new site in future - it formats the questions and answers better.