-1

Trying to connect two active DB for reporting both running SQL. Recently migrated several customer groups to the new DB but now need to run comparative historical reports. How do I get all data from both db, joins filter out newly added customers on either side based on the join used. Is this possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BobM
  • 1
  • 2
  • did you take a look in here first? http://stackoverflow.com/questions/12457947/merge-multiple-databases-into-a-single-database –  Nov 23 '15 at 16:21
  • can't you just put the database name in brackets in front of the table name? (i.e. [databasename].[tablename] in a select) as for filtering on the new data, you'd have to filter by date added or maybe if you had a bit set for "Migrated" or something... can't think of any other way. – swinkel Nov 23 '15 at 16:22
  • This depends on the size of your data. If your database are really large, then you would want to use ETL for this rather than SQL Queries. – Raj More Nov 23 '15 at 16:36

1 Answers1

1

If they are on two separate servers, you will need to link the servers to each other first. This link should set you in the proper direction. Then you need to reference them using 4-part BOL: 

SELECT T1.*
FROM [Server1].[Database1].[dbo].[Table1] T1
LEFT JOIN [Server2].[Database2].[dbo].[Table1] T2
ON T1.MyField = T2.MyField

If they are on the same server, you only need to add the database name to your SQL code. So, if you're trying to link data from Table1 in Database1 to Table1 in Database2, you would do this:

SELECT T1.*
FROM [Database1].[Table1] T1
LEFT JOIN [Database2].[Table1] T2
ON T1.MyField = T2.MyField
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117