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, Yanoroouh...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...|||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)|||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...|||Okay, I will try that. Thank you very much.
I may encounter another questions... I will let you know.
Yanoroo|||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|||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...