I have created a database to hold Operator Transactions from a Building Management System. This data is stored in a dBase IV (*.dbf) file.
The Data has several columns but for the purposes of this Post, here is a sample of the data:
TRANTYPE OPERATR GROUP SYSTEM DATE_Y DATE_M DATE_D TIME_H TIME_M TIME_S
0 DFB B77 77-HHW 106 12 25 18 36 22
1 WSS B98A 98-CC6 106 12 25 20 45 05
1.) I need some advice on creating a meaningful Primary Key. I thought about using the date and time, but there would be duplicates. See, once I import this initial data, I will import the Operator Transactions periodically and I want to be able to reject any duplicates. We store approximately 3 months of OpTrans in a file before we overwrite it. If I upload to SQL Server every month, there will be many many duplicates that I want to skip. Any Ideas?
2.) I'm also thinking I should combine the Date & Time Fields into one field so I can search for info using SQL Server's native datetime. I found that the following SQL gives me what I want
SELECT DateTime = CONVERT(DateTime, CONVERT(VARCHAR, Date_M) + '/' + CONVERT(VARCHAR, Date_D) + '/' + SUBSTRING(CONVERT(VARCHAR, Date_Y), 2, 2) + ' ' + CONVERT(VARCHAR, Time_H) + ':' + CONVERT(VARCHAR, Time_M) + ':' + CONVERT(VARCHAR, Time_S))
Order By DateTime
Any Advice on how to do this? Should I use a transform in DTS - if so, how?