Solved

Import data from one sql server db to another

Posted on 2013-06-14
5
315 Views
Last Modified: 2013-07-06
We have purchased a company that uses the same software as us. The software runs off a SQL 2008 database. We want to import the data from the acquired companies db into our db. There are only a few tables that we need to import (Customer, Employee, BillingProfile). What is the easiest way to do that? My biggest concern is the primary keys on each table. Obviously they are sequentially different so how do I get the imported data to fall into the sequential order of our db?
0
Comment
Question by:clifford_m71
5 Comments
 
LVL 22

Expert Comment

by:Haresh Nikumbh
ID: 39247784
check below link if its works for you

http://www.codeproject.com/Questions/496992/Howplustoplusimportplusdataplusfromplusoneplussqlp

Please be inform before importing on actual server i will suggest test this on other server. if everything works fine then you can try on actual server.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39247833
How do you assign the primary keys in your environment?  Is it with an identity column?  (i, personally, would be more concerned about the foreign keys)
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39247852
This is going to be application specific.  What I've done in the past is to import the data into each table using a CURSOR and generating a new key.  I then create a new "mapping" table that records both the old key and the new key (I would do this for Employees and Customers)

Then, when I go to import subsequent files, I'll do a lookup on this mapping table to make sure data in say, the new entries Billing table, maps correctly to the new Customer data.

I've always done these types of imports row-by-row, using CURSORS (gasp!), even though I know it is slower to do so, but I do that so I know (and can easily print debugging messages) that everything is getting mapped over correctly.  If you were doing this import on a nightly basis I'd insist on trying to figure out an efficient way of doing it, but if this is a one-off, just get it done in whatever manner, as long as you can ensure you have data integrity.
0
 

Accepted Solution

by:
clifford_m71 earned 0 total points
ID: 39289770
Thanks for your suggestions. I ended up exporting the new data into excel. Changing the appropriate primary and foreign keys to whatever was next sequentially in our existing database then essentially did a select into line by line. By having it in excel I was able to copy several lines at a time. This was time consuming and tedious but got the job done.
0
 

Author Closing Comment

by:clifford_m71
ID: 39303669
It worked
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

11 Experts available now in Live!

Get 1:1 Help Now