troubleshooting Question

Database Design Advice

Avatar of LenCepeda
LenCepeda asked on
Microsoft SQL ServerDatabases
4 Comments2 Solutions63 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros