Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hamzah
Starting Member
4 Posts |
 Posted - 2006-02-28 : 11:08:48
|
I have a design question. What I have? I'm getting unique records from a supplier once in every three months (I can identify unique key) and i'm creating a database to normalise and store those data dumps. The next data dump might contain records from an existing data dump. What I have to do?I need to provide the records with a primary key value(eg. ID), so that I can trace them by using this ID field inside the database and also when the next data dump comes in, I should know whether the records are already present in the system and if they are I need to consider the ID value which is previously assigned for them from the ID field and if the records are not present in the previous data dump, I need to provide them with a new ID field value.I'm thinking of creating a seperate table and using an IDENTITY column for the ID field.So everytime the data dump comes in, I need to check with this table and get the ID value if they are present or create a new one if they are not?QuestionAny other suggestions?Thanks for reading my post and I appreciate your patience.Sorry for the long post |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-02-28 : 15:04:30
|
| How do u get data from supplier (Text file, a record set, an Access table, an Excel Sheet ... ?)How do u know that the records are unique ? Does it have a unique identifier or some fields makes the record unique ?Is ur concern is to put an ID value (if there is no record) only ?ie. Checking each record (means all fields) u received with all records (match field by field) to c whether there r any matches ? |
 |
|
|
Hamzah
Starting Member
4 Posts |
Posted - 2006-02-28 : 16:07:02
|
| Thank you for answering.How do u get data from supplier (Text file, a record set, an Access table, an Excel Sheet ... ?)I get it in Access table. I usually import that in SQL Server 2000 for quering.How do u know that the records are unique ? Does it have a unique identifier or some fields makes the record unique ? There is a unique identifier(eg Rid) in the data dump, but the problem is that Rid might occur as duplicates in the data dump. The main purpose of using the ID field is query with different databases.(The Rid field is a sensitive field like SSN, it should not be used outside the database, that is the main reason Y I'm looking in creating this ID field)Is ur concern is to put an ID value (if there is no record) only ?ie. Checking each record (means all fields) u received with all records (match field by field) to c whether there r any matches ? Yes, If they are not present in the system then assign them with new ID value. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-02-28 : 16:49:27
|
| u can have a DTS package or a stored procedure to -- check the RID of source & destination-- if RID exists in both, to use existing UrID (do update or whatever) else to generate the next UrID (and do an insert or whatever)eg.Insert Into MyDestinationTbl (<fields list>)Select IDGenerateFunction(), (<fields list>) from SourceWhere RID not in (Select RID from MyDestinationTbl) |
|
|
|
Hamzah
Starting Member
4 Posts |
Posted - 2006-03-01 : 16:14:51
|
| So if I use a DTS package, I dont have to store the datadump inside the database.I guess thats an effective way rather than using a stored procedure(which requires the dump to be inside the database) |
|
|
|
|
|
|
|
|
Assigning primary key to records