All Forums Database
Rajasan77 10 posts Joined 07/09
17 Nov 2009
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 12300The 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.

Jimm 298 posts Joined 09/07
17 Nov 2009

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 (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.

You must sign in to leave a comment.