Solved

MS SQL Inserting Data Best Practices

Posted on 2014-03-07
4
384 Views
Last Modified: 2014-03-07
I'm trying to find some best practices for importing data into a SQL table.  Not just any SQL table, but an existing table that is used in a complex application.

What I mean by complex is that the table may have many constraints or a trigger or some other unforeseen complication.

I tried a few google searches, but I'm just finding results on how to do inserts.

I know that one cannot just blindly start importing data into a system because all kinds of havoc can be done without careful planning and data quality checks.

How does this apply to different databases such as MSSQL, MySQL, DB2 Oracle, etcetera
0
Comment
Question by:Jeff Darling
  • 2
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39912601
Make a (temporary) table(s) with your new data.
Then duplicate your production db into a staging one.

Then use
INSERT into staging_table
    SELECT fields FROM new_table

Open in new window


This will insert data checking for indexes and constraints.

Once you're convinced all is good, replace the production db with the staging one.

HTH,
Dan
0
 
LVL 12

Author Comment

by:Jeff Darling
ID: 39912753
Thanks Dan, thats a great idea.  that way Unit testing can be done.  It also is helpful to have a development environment.

as soon as I posted this, I tried a few more queries in Google and found some good best practices for SQL in general.  I can look at those and extrapolate from there.
0
 
LVL 12

Author Closing Comment

by:Jeff Darling
ID: 39912758
Thanks for the feedback and your patience.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39912826
Glad I could help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now