SQL Merge 2 Databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » SQL Merge 2 Databases

SQL Merge 2 Databases

Rate Topic

Display Mode

Topic Options

Author

Message

davdam8

Posted Friday, September 28, 2012 9:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 163, Visits: 612

Hi geniuses!
I need to merge 2 databases to colect information for my reports.

Have really no clue about how to set things up.
The query must be written into the query 'box' of the Dataset Properties, RIGHT?

Thanks in advance.
Regards!

Post #1365953

anthony.green

Posted Monday, October 01, 2012 1:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

What it looks like you need is a UNION clause, either in a stored proc on direct in the data set.

SELECT
1,2,3
FROM
DB1.dbo.Table2
UNION / UNION ALL
SELECT
1,2,3
FROM
DB2.dbo.Table1

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366362

davdam8

Posted Monday, October 01, 2012 3:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 163, Visits: 612

OK. Thanks!
It is possible to relate 2 tables from different datasources?
For example: I have Dataset1 from Datasource1 and Dataset2 from Datasource2.
I want from Dataset1 : ProjectName;
and from Dataset2 : ProjectStage. Which is in another DB.

How do I do this and add these fields into the same tablix?

Sorry if it sounds stupid, but I'm learning, this is all new for me.

Thanks in advance.
Regards

Post #1366425

anthony.green

Posted Monday, October 01, 2012 3:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

Not sure on that, you will need to build some sort of linking in 1 of the servers.

Something like using linked servers, or building a merged database of both DB's using SSIS or something along them lines.

Post #1366427

davdam8

Posted Monday, October 01, 2012 3:49 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 163, Visits: 612

OK thanks anyway!
What about linkin 2 tablix? Possible? Crazy stuff right?
Regards.

Post #1366445

anthony.green

Posted Monday, October 01, 2012 7:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

again unsure, never had to do it, would be worth a google and if it is possible let us know how.

Post #1366579

davdam8

Posted Monday, October 01, 2012 8:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 163, Visits: 612

OK! In the mean time how do I create a Stored Procedure where I want data from 2 Databases.
I want ProjectName from DB1 & ProjectStage from DB2.

Thanks

Best Regards

Post #1366585

anthony.green

Posted Monday, October 01, 2012 8:09 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

Something like the below

SELECT
DB1.ProjectName,
DB2.ProjectStage
FROM
Database1.dbo.Table1 DB1
INNER JOIN
Database2.dbo.Table2 DB2
ON
DB1.ProjectStageID = DB2.ProjectStageID

Without knowing your data schemas it is hard to say.

Post #1366588

davdam8

Posted Monday, October 01, 2012 9:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 163, Visits: 612

This not working for some reason.

Instead, do you know a way to link different datasets from 2 different datasources in a tablix cell expression?
Dataset1 from Datasource1
Dataset2 from Datasource2

Thanks

Regards

Post #1366639

anthony.green

Posted Tuesday, October 02, 2012 1:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

Google it, I am unsure if it can be done.

Post #1366885

« Prev Topic | Next Topic »

Permissions