Importing all Data from one SQL DB to another

Experts,

I have a large software application using a SQL 2008R2 DB.  I also have a parallel installation of the software (the next version up) as well as a parallel copy of the DB.  The schema for the two databases are nearly identical, but there are some tables with slight differences, additional columns etc. What I need to do is attempt to copy as much data to the new DB as possible and I can manually go through later and query the remaining pieces as needed.

On the new DB, II need to be able to:
-Drop all Constraints
-Allow ID Insert
-Import all data from each table into the new table where tables are identical, and skip data where certain fields do not exist in one or the other table.
-Recreate all Constraints after import.

I have tried using SSMS Import / Export functions all day, and cannot even get the Old DB to export without errors to any* format. (I have tried SQL to SQL, SQL to Excel, Access, Flat File....nothing works. There are about 200 tables in the DB, so Im trying to avoid going table by table.

Does anyone have suggestions?

Thanks in advance!
hhnetworksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>but there are some tables with slight differences, additional columns etc.
Before doing a 'grip it and rip it', and excellent idea would be to compare the two databases and get all differences.

Open SQL Server Data Tools
SQL menu > Schema Compare > New Schema Comparison
Set the Source (left side) and Target (right side) connections to each db
Then hit the Compare button.  

The result will be a script that shows every difference between Source and Target database.
lcohanDatabase AnalystCommented:
"What I need to do is attempt to copy as much data to the new DB as possible and I can manually go through later and query the remaining pieces as needed."
Don't you need the schema's to be identical first?
And for the data I would just use use "COPY DATABASE" feature under "Tasks" to do that between the two SQL instances but old (tried/trusted) FULL Backup/Restore trick should do the same isn't it?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hhnetworksAuthor Commented:
I learn something everyday.

The SQL Schema Compare in VS2013 is the trick. Both of you are correct; I should get the schemas identical first.  I had not used this tool before, so I appreciate both of your answers!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.