Solved

Import data from one sql server db to another

Posted on 2013-06-14
5
318 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - SQL field is defined as text 3 37
sql Total query 2 27
SQL Server 2012 r2 - Sum totals 2 25
Sql Server group by 10 27
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 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