Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009

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

  2. #2
    Join Date
    Jun 2010
    No way of doing this using bcp, however u got the solution. Use replicate function without where clause. That should update all values which are less than 5 char long.

  3. #3
    Join Date
    Jun 2010
    is short, use below query:
    update AAA
    set AAA_code = replicate('0',(5 - char_length(rtrim(AAA_code)))) + AAA_code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts