loading data to table that has 2 columns that are foregin keys


  • I have a new sql server 2008 r2 database that I am in the process of setting up. I would like to know the best method for loading data daily to table that has 2 columns that are foreign keys for two other tables. The data will be loaded to this history table daily and will be appended to the end of the table.

    Thus can you tell me and/or point me to a reference that will show me if I would use an alter statement, possibly drop and recreate the table will all the data, use a truncate table statement and load the data? What do you suggest?

    Friday, March 30, 2012 4:19 PM


All replies

  • Hi, if performance is the main concern check the article below to see what's the best method for your case.

    The Data Loading Performance Guide


    Friday, March 30, 2012 4:25 PM
  • Could you tell me if I should drop an index or what? This application is a side note from the main processing. it will stand all by itself.
    Friday, March 30, 2012 7:20 PM
  • There are many variables to this issue, and the "best" option depends on how often you'll use said table, how you'll query it, exactly how you load it, and other things.  If you can tell us some of these things, then we can give you pointers.  Otherwise, the best we can do it point you to external resources.
    Friday, March 30, 2012 7:25 PM
  • How much data is being transferred?


    Friday, March 30, 2012 9:57 PM
  • I will be loading about 4,000 records a day.
    Monday, April 02, 2012 4:25 AM
  • Hi Wendy,

    That appears to be a low volume for the mighty SQL Server. I don't think you have to do anything special. The following article deals with disk resources allocation:

    Fast tempdb is the secret of top SS performance.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Monday, April 02, 2012 5:43 AM
  • I am not concerned about performance now. I just would like to see some example sql of how load the history table. The entire process is brand new. All the tables are new.

    There is not a main table and a history table. there is just a history table.

    Right now I am creating the 'control' tables that will be used as a reference to the history table. the history table will have foreign keys that point to the control table.

    Thanks for your help!

    Monday, April 02, 2012 7:41 PM
  • If data volume is low (you have mentioned 4000 rows), then you can use simple INSERT INTO...SELECT ...statements to load the data to history table. If volume is huge then you need to consider other options like partitioning tables, disabling indexes, bulk loading stategies to name a few...

    - Krishnakumar S

    Monday, April 02, 2012 7:49 PM