Question on Insert


Question on Insert

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.

Re: Question on Insert

I assume your data is comma delimited, variable width.
Use Multiload to load it - it allows SQL functions directly in the loading.

.Logtable Logtable002; /* Connect to the Teradata DBS */
.Logon tdpx/user,pwd; /* identify the restart table */
Create table Claims /* Create the history table */
( Ext_Id char(3) ,
Group_Id (Char(8),
Claim_Number (Char(12)
Unique Primary Index (Claim_Number);

.Begin Import Mload /* Specify MLOAD IMPORT task */
tables /* and identify the */
Claims ; /* target tables. */

.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
, Group_Id
, Claim_Number)
( 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.