Unanswered: Data in the table should be the format of the table
I have a file which looks like this (comma delimiter file) :-
The table AAA looks like this :-
AAA_code not null char(5) primary key
AAA_service varchar(3) primary key
I am bcping the comma delimiter file into the table AAA.
But i want to make sure the data goes into the table contains all 5 digits for the first column AAA_code and all 3 chars/digits for the second column AAA_service
Now in the above file records .... the last record 591,001 should be in the table has 00591,001
bec., the records of the table r used for further processing .
Now without seeing the input file and without looking at records in the table , how to achieve the correct size of the columns .
After searching some topics in this website , i found replicate function has the answer ..but i should not update each individual record which doesnot meet the size of the column .
this is a specific update , but i dont want this (as it is specific to that record and this can be done only when we find the record ?)
update AAA set AAA_code = replicate('0',(5 - char_length(rtrim(AAA_code)))) || AAA_code where AAA_code = '591'
I want something , which will automatically check the data and the column size is correct , if not it updates that record .
Pls anyone help me ....little urgent work to be finished .
Thanks in advance ...