We help IT Professionals succeed at work.
Get Started

Database Design Advice

60 Views
Last Modified: 2016-01-11
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.
Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 2 Answers and 4 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE