Database Concatenation

I have two databases with the exactly same structure (schema). The structure includes foreign key constraint and identity columns. One database contains heavy data and the other contains light data. I want to combine the two separate databases into one database. What would be a better way to do it? How should I write script to do it?

Actually I'm thinking about how I should handle the identity columns and keep the relationships.  

Who is Participating?

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

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.

Use Union or Union All, if you have the same number of columns:

Select Col1, Col2, Col3 from Table1
Select ColA, ColB, ColC from Table2

Union All allows duplication but Union retrieves distinct values.
Bhavesh ShahLead AnalysistCommented:

If your both table contains identity and you wanted to maintain that then

i suggest create one more column to maintain identity of both table.

- bhavesh
Alpesh PatelAssistant ConsultantCommented:

I schema of tables are same use Union.

And in destination database create one columns in each table for source identity.
Please make sure Primary key is composite key with Source identity + Table identity.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

minglelinchAuthor Commented:
Thank you all for the comments which give new knowledge.
I really do not want to create a new column. It's a production database.
If you have tables with the same structure then union should help you, as per my above post.
minglelinchAuthor Commented:
If do a union, how about some rows may have the same identity values in the two databases?
Anthony PerkinsCommented:
>>If do a union, how about some rows may have the same identity values in the two databases? <<
That could prove a bit of a problem.  Incidentally, contrary to popular myths, you can actually have duplicate values in a column that has a IDENTITY attribute, but that is not going to help you very much here.  You need to ensure that all the values and most importantly their relationships are consistent.

Here is what I would do.  Move the smaller database into the bigger database by changing all the IDENTITY values to a negative number.  In other words if your IDENTITY column is 12345 make it -12345.  If you are relying on the order of the values than another idea is to define all the IDENTITY values merged in as offset with a large negative number, for example instead of 12345  Make it 12345 - 1000000.  This way all the values are consistent.
- if you have same structure such as table name, column name, private key for both database, why not combined the data from lighter table into the similar heavy table instead?
- this can be achieved by using INSERT INTO SELECT syntax as follows:
INSERT INTO database1.dbo.table (column1, column2)
SELECT column1, column2 FROM database2.dbo.table
Anthony PerkinsCommented:
>>why not combined the data from lighter table into the similar heavy table instead? <<
"some rows may have the same identity values in the two databases"
- i would then suggest to put a check condition to the INSERT INTO SELECT statement above by specifying a WHERE clause - for instance WHERE PrivateKey from database2 NOT IN (Select PrivateKey from database1)
Anthony PerkinsCommented:
I am going to let the author explain, but I suspect they want to insert all the rows and not just the ones that do not match.  The primary keys are surrogate keys and therefore the same value in the two databases cannot be treated as the same.

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
minglelinchAuthor Commented:
Yes, I  insert all the rows and not just the ones that do not match.  I gave up this task, but I will leave this question open for further discussion for a while.
minglelinchAuthor Commented:
Thanks for all comments. Force to close.
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.