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 |
|
patla4u
Starting Member
10 Posts |
 Posted - 2011-05-14 : 21:58:50
|
| I have to load monthly data in sql. Data comes from flatfile and loading in staging sql table through SSIS.there are 16 tables. I don;t know relation with each table.the main purpose of these tables to create reports andthere is no relation ship in staging table.need right direction.....Now I want to load data from staging to Production ....so1.I need to created relation with each table?? like primay key and foreign key then load the data ???need right direction.....2.Data comes every month so it will be duplicate so ...how can i load duplicate record ??.,,,,,I want duplicate data......so like any solution to create composite primary key is the right solution.?????liek create another column and year and make as a composite primary key .,,????is there any other way??thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-15 : 05:52:06
|
| A lot of how you should do depends on the business needs. At the very crude level, you can simply copy the data to production tables and say that you are done. You might go one step further and add an identity column and make it the primary key. You may also want to add date column to each of the production tables, so you can know the date on which you imported the data.But, ideally you would want to understand the business logic, how the data tables relate to each other, create the proper foreign keys and primary keys, and then insert the data. This would allow you to maintain data integrity and catch errors.But, as I said earlier, it depends on your business needs; I am just talking general principles of what I would do if I were faced with this situation. |
 |
|
|
patla4u
Starting Member
10 Posts |
Posted - 2011-05-15 : 20:21:10
|
| Thanks for replying me,my business rek in to generate a reports....Yes,,,I have created One staging database. In This staging database, I have all 16 tables.The thing is that,we do not want normalization...I have loaded only one month data in staging.....now i want to load the data in production,,,but before that ,,,,you said ,,,, I have to create relation with all tables.. ..Ok I will contact business person for that.once one month data will load in production...then load second month data.......but data will duplicate ... And I want duplicate data.....I don't want remove duplicate data because of reporting is req.......Please let me know....How to do that ....Thanks |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-16 : 07:11:07
|
| Everything I said in my earlier post is general idea of how one would go about doing this. To be more specific, you should post the DDL (data definition language) for your tables. Pick one staging table and corresponding production table and post the DDL for those. Brett's blog http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx has instructions on how to get the DDL.If you have duplicate data, that should not be a problem as long as the constraints on the table allow it. You may need to insert some additional information into the production table such as month for which the data was imported. |
|
|
|
patla4u
Starting Member
10 Posts |
Posted - 2011-05-16 : 21:04:53
|
| Thank you so much,,, I will insert 2 more column in production...one for month and one for year...for remove duplication.Thanks again,, |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-05-16 : 21:17:55
|
I think already mentioned by Sunita. I would go for Adding a new Identity Column for a table as Primary Key, if i dont have any restriction for using identity column as to be PK. This would allow duplication into a table. Also, it will be using little space than adding two columns! However if you need to track when the data is added into the table then you may add a column DateTime Column along with Identity Column :)However please note that all this is based on the assumption in light of our perception of you problem.  CheersMIK |
|
|
|
patla4u
Starting Member
10 Posts |
Posted - 2011-05-18 : 18:58:48
|
| Thanks MIK I got it.I will need to track each month data....So I am thinking like create another extra column for month and make as composite primary key...I will not make composite primary key with month but I will create for tracking data.OK I will create identity column also.But I really don;t know,(How can i join table with identity column,,),,,No Idea,,,How can I create primarykey and foreign key ??Please advise me,ThanksB |
|
|
|
|
|
|
|
|
need right direction