Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Import data from one sql server db to another

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
clifford_m71
Asked:
clifford_m71
1 Solution
 
Haresh NikumbhSr. Tech leadCommented:
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
 
didnthaveanameCommented:
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
 
nemws1Database AdministratorCommented:
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
 
clifford_m71IT ManagerAuthor Commented:
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
 
clifford_m71IT ManagerAuthor Commented:
It worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now