SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


database table design


database table design

Author
Message
selvakumar.sms
selvakumar.sms
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 457
hi all

i am getting software asset from different client machine nearly 2000 client per day,i am getting data in text file and uploading in database.my file look like this first day.
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Cisco WebEx Meetings
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe AIR
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe Flash Player 10 ActiveX
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20120612|Audacity 2.0
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20110310|AVI Media Player 1.0.1
den second day , same file with additional column

SOFT|GE-DT-0049|000FFE0E68AE||2012-08-16 12:59:45Z||Cisco WebEx Meetings
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe AIR
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe Flash Player 10 ActiveX
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20120612|Audacity 2.0
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20110310|AVI Media Player 1.0.1
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|Babylon toolbar on IE
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Belarc Advisor 8.2
SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||CCleaner
here no primary key, i dont know how to design my data table,

first day i am inserting all the data in table, second day also inserting all data with additional rows(i means new rows).now duplication is there,already existing data is repeating in second day insertion.

please help me out.

--------------------------------------------------------

I am Learner -- SQL
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14008 Visits: 15944
If there's no primary key, there's no reliable way of preventing duplicates. The first thing to do is to import from your text file into a staging table, and clean the data up in there, so that you have each column contains the same sort of information (dates, application names, etc). Then you can look at what makes each row unique, and use an outer join or some other technique to avoid importing data into your main table that is already there.

John
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5337 Visits: 4639
Shall we understand that...

1- Your system is cumulative loading of a particular table.
2- Each day you get new rows that have to be inserted.
3- Each day you get updates to some columns of existing rows.

Is that correct?

If the answer is yes then your system should look like this:

SourceDocuments ==> StatingTable ==> CoreTable

SourceDocuments represent the files you have to load.
StatingTable has to be truncated before each "load" and should include ALL columns you want to capture from the SourceDocuments.
CoreTable represents the place you want to keep data up-to-date. As already posted you have to figure out a way to identify each row uniquely (PK) then your load process has to either INSERT or UPDATE rows depending on existance of the row or not on CoreTable.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search