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?
Loading
SenthilkumarPosted Mar 30, 2012, 10:45 PM
I am bit confused..
If you want to maintain audit of the table then you can go for Trigger. It will update the activities into the history table.
If you want to take the backup of the data then you can delete the rows from the table and insert all the data.
This can be done in the sql server scheduling jobs.
DELETE * FROM History
INSERT INTO History(ProductID, ProductName, Manfacturer, Amount)
SELECT ProductID, ProductName, Manfacturer, Amount FROM Products
The above statement will remove all the records and take the backup from the products table into History table. This will became routine work and you can set up this routine job on daily basis based on your business time.