Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    18

    Unanswered: Primary key question

    Hi,

    I am new to SQL 2000 and scratching my head...I am importing text files to SQL 2000 tables. How should I define the primary key in a table if the text file is :

    Example 'customer invoice file'
    column 1-Customer Bus Unit (Char 3)
    column 2-Batch number (Char 6)
    column 3-Document type (Char 1)
    column 4-Invoice number (char 15)
    column 5-Invoice dollar (Money)
    column 6 - due date (date)
    column 7 - customer name (char 35)
    column 8 - customer address (char 50)

    The primary key of this file is column1 + column 2 + coumn 3 + column 4. That concatnation of 4 fields make each record unique.
    Should I define all 4 feilds as the primary key field? Thank you, Yanoroo

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    uh...yes...

    Do you have a staging environemnt?

    You can create work tables with no constraints...that way you can audit the data before it hit the final destination...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    18
    Originally posted by Brett Kaiser
    uh...yes...

    Do you have a staging environemnt?

    You can create work tables with no constraints...that way you can audit the data before it hit the final destination...
    Please bear with me...
    What do you mean a staging environment?
    Could you suggest a work table design? (example)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sure...it's a table that looks like your final table...just that it has no keys, indexes or other constraints....even make all of the columns varchar (dates, money, ect)

    You can the audit the data

    LIKE

    SELECT * FROM myTable99 WHERE ISDATE(adatecolumn) = 0

    Will show you all rows with bad dates

    SELECT * FROM myTable99 WHERE ISNUMERIC(anumericcolumn) = 0

    Will show you all rows with non numbers in the expected number columns

    PK violations

    SELECT col1, col2, col3, col4, count(*)
    FROM mytable99
    GROUP BY col1, col2, col3, col4
    HAVING COUNT(*) > 1

    Shows where you'll have a dup key

    ect


    You can even make sure RI is ok...


    If the data passes your audit, you can either bcp/dts or bulk insert the file, or Just INSERT it from your stage table...

    Clear as mud?

    Let me know...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    18
    Okay, I will try that. Thank you very much.
    I may encounter another questions.... I will let you know.
    Yanoroo

  6. #6
    Join Date
    Dec 2003
    Posts
    18
    Brett, are you still checking?
    Not only the staging the work, I tried your PK violation check and it worked beautifully.
    I appriciate your help. Yanoroo

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey.....Merry Holiday

    I'm glad it worked for you....It's always better to know that you're working with clean data...

    TRUST NO ONE bearing Excel spreadsheets...(or other data sources outside your environment)..
    Better still require data files with header and/or trailers...and if you can only get one...demand a trailer...with record counts and possible the sum of int/decimal columns...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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