Solved

Import data from one sql server db to another

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

Expert Comment

by:Haresh Nikumbh
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It worked
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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