[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Import data from one sql server db to another

Posted on 2013-06-14
5
Medium Priority
?
339 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

825 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