Help importing dbase IV to SQL Server

Last Post 27 Dec 2006 05:52 AM by billthecat0702. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
billthecat0702
New Member
New Member

--
27 Dec 2006 05:52 AM
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))
FROM OpTrans
Order By DateTime

Any Advice on how to do this? Should I use a transform in DTS - if so, how?
You are not authorized to post a reply.

Acceptable Use Policy