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 |
|
jazzgirl
Starting Member
6 Posts |
 Posted - 2012-04-01 : 17:26:31
|
| Basically I am tyring to decide how to load the history table on a daily basis. (The history table will contain all the daily transactions.) There will be 3 extra tables used to validate what data is loaded daily into the history table. The 2tables will be 2 foregin keys in the history table. The history table will be linked to the 3 other tables by having a foreign key for each table.How should I setup the bestg sql script? Will I drop the foreign key relationships first before I load the history table daily? Will I load the history table daily and not drop the foreign relationships. What is the best method of referring to the other 2 control tables? Do I just use an alter statement? Can you tell me and/or point me to a reference I can use to solve the problem? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-02 : 00:19:52
|
| you can add lookups to other tables to make sure referential integrity is maintained before inserting to history table. Dropping foreign key is not recommended as it will cause it to load data if even it fails referential integrity condition------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
jazzgirl
Starting Member
6 Posts |
Posted - 2012-04-02 : 00:35:56
|
| Can you tell me what you mean by, "you can add lookups to other tables to make sure referential integrity is maintained before inserting to history table"?Can you tell me about the lookups and/or point me to a reference about what you are referring you? Can you give me some sql examples of what lookups do you before inserting rows into the history table? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-02 : 00:42:04
|
| how are you currently doing population of history table? is it through ETL tool or through sql script?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jazzgirl
Starting Member
6 Posts |
Posted - 2012-04-02 : 15:19:16
|
| I am not loading any data to this history table now. This is a brand new table. I am setting up a brand new process. Also there is no main table and history table. There is just a history table.I will have a few foreign keys in the history table that points to tables for referential integrity. All the new table are brand new also.there will be between 3,500 to 4,500 records loaded to the history table daily. How would you setup this sql? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-02 : 15:48:15
|
| you can set it up in sql or use a etl toolIn sql, you can make use of joins for doing lookupif you want perfect match use inner joinfor identifying missing relationship you can use left join and check for nulls------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jazzgirl
Starting Member
6 Posts |
Posted - 2012-04-03 : 10:35:02
|
| thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-04 : 18:30:49
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
table with foreign keys