how to combine data from 2 different database into one for sql?

[Semidevil]

I have a lot of data to work with and for whatever reason, half of my tables and data are in database A. The other half are in Database B.

how do I add on the data from B to A? Database A is completed. Do I just copy the code from A, modify it to fit B, and paste it back into the queries for A?

 

[Ken g6]

Most SQL databases allow dumping their data, and restoring it later. Maybe you could dump one or both databases and combine them?

 

[KB]

What RDBMS are you using? SQL Server and Oracle allow you to link servers, so you can join a table from one server to a table on another with one query.

http://stackoverflow.com/questions/...d-query-linked-database-servers-in-sql-server

http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12084

 

[maluckey]

Not sure what you are trying to accomplish. If you're trying to merge databases, as alredy mentioned, a database dump is the way to start.

In my case, it's business dictated that certain data is in certain boxes. As the solution, we use CMS (available in SQL Server 2008-present) to query against the multiple data sources.

 

[Markbnj]

There are a whole bunch of ways to do this. The specific mechanics depend entirely on which server you'e using, and so posting your query without giving that information was a waste of your time. Not a waste of my time, because I was just reading this board, but definitely a waste of yours.

 

[Scarpozzi]

You can export tables and simply import them into the DB you want to keep. I'm sure someone can google it for you if you tell us what you're using. MySQL, MSSQL, Oracle, PostgreSQL, etc...

If you're worried about corrupting data, you can always try cloning one of the DBs before you start and name it what you want as your final name....then export tables from DB2 and import them back in....as long as there are no collisions in naming conventions, you should be fine.

 

[Semidevil]

Thanks for the replies. I'm using Microsoft sql server. So more specifically, let's say database A has data for 10 u.s states. Database b has data for the remaining states but the data are in different tables when compared to A. Database A has a fully functional query that executes fine and loads the tables that I need. Basically, I want to add the info from B onto A.

Right now, I'm copying the query from A onto a blank sheet,and am just adjusting the code to find the right data from B. Would that be the simplist way to do it? Once I'm done, can I just add the B code over to the the query for A and 'insert?'

S

 

[Sgraffite]

You can insert into A while selecting from B:

INSERT INTO [A]
([Column1], [Column2])
SELECT [Column1], [Column2]
FROM [B]

 

[maluckey]

I'm using Microsoft sql server. So Right now, I'm copying the query from A onto a blank sheet,and am just adjusting the code to find the right data from B. Would that be the simplist way to do it? Once I'm done, can I just add the B code over to the the query for A and 'insert?'

S

If this is what you are doing, and not trying to physically merge the two boxes, just use CMS (already in SQL Server), and write ONE query and be done.

If you need a flat table for some reason at the end of the procedure, you can load it to a temp table or a view at the end and do your processing from there.

M

 

[Semidevil]

Thanks. So right now, what I'm trying to achieve is to update the report once, and have it all compiled in one table.
Let's say I update the report weekly. The current situation is that I would open up the query on table 1, update the date, review it in excel. For table 2, I would update the query and review it in excel. Since it is in 2 separate sheets, I can't do an overall analysis on the report .

Ideally, I want everything to be merged together, I update once, and everything is in one table.

 

[Sgraffite]

You could possibly make a view that selects from both tables. Or you could set update triggers on A and B to updated a third table which contains all of the data, essentially a materialized view.

 

[sao123]

this sounds like a process that needs ETL?
have you checked into SQL Server Integration Services? (SSIS)

 

[clamum]

I would create a linked server on database server A to database server B. Creating a Linked Server

Then you could either just query from both databases in one query:

SELECT *
FROM myTable a
INNER JOIN [databaseServerB].DatabaseName.dbo.myOtherTable b on a.someField = b.someField

Or copy the data from database B and then you'd just query database A:

INSERT INTO a (field1, field2)
SELECT b.field1, b.field2
FROM myTable a
INNER JOIN [databaseB].DatabaseName.dbo.myOtherTable b on a.someField = b.someField

I've also used the "Import Data" feature in SQL Server. You'd need to create the Linked Server first, I think, and then on database A you can right-click the database you want to import data into and select "Tasks" -> "Import Data..." You can Google how to do it, or follow the steps, but basically you write a SQL query that selects the data in database B and use it to copy the data into database A.