Solved

MS SQL Inserting Data Best Practices

Posted on 2014-03-07
4
383 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

746 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

13 Experts available now in Live!

Get 1:1 Help Now