Hello - Please let me start by mentioning I am completely new to MS SQL server and database design. I have read a DB design book and looked at a few online tutorials.
Here goes. I am attempting to import a file (.csv) from another system on a nightly basis into a new MS SQL 2012 DB (Enterprise Ed). Once the CSV is imported and formatted as a table (Sales) it will be related to one other table (CustomerID) within the DB. I would like the CSV Import to append new data to a pre-existing table and overwrite any records within that table that have like "SalesID" number.
So far, i setup a MS Server 2012 VM, installed MS SQL Server 2012, Created the DB. successfully manually imported a CSV into the DB to create the initial SALES table and now I am researching solutions that will allow me to import newly created CSV on schedule. It looks like SSIS / Bulk Insert should do the trick so i installed those feature and began to research next steps.
Once the DB is complete, it will be connected with MS Excel Powerpivots that users can access and refresh. I have some experience establishing those types of files so i think we will be fine once we work our the basic DB.
1. Am I on the right track?
2. Should the imported CSV's go directly into the pre-existing table or should that data get imported into a new table and merged using another process?
3. Once the data is merged in the "master" SALES table, should it get copied elsewhere to allow the Powerpivot connection or is it ok to allow the Powerpivot to connect directly to the "master" sales table
Thank you, any advice would be greatly appreciated.
2. The CSV import should go into a staging table, not the main data table(s). Staging tables are extremely common.
3. It's ok to allow Powerpivot to use the main table (for reads only). If necessary, you can use snapshot isolation later to speed it up.