Solved

MS SQL Inserting Data Best Practices

Posted on 2014-03-07
4
386 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

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 35

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Persistent Connections 10 32
How can I find this data? 3 23
Access #Deleted data 20 38
SQL syntax question 6 38
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

735 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