SSRS report - Merge data from different datasources with the same tables?

Question

I have 2 databases, one is for Employees, and the another one is for Employees that used to work on the company. both databases have the same tables but with different data (there is no Employee_ID that will be in both databases). let's take only employee table. The fields that I have in employee table is Employee_ID First_Name and Last_Name. I want to show them both in the same table: Employee_ID First_Name Last_Name ________________________________________________________________________________ Employee_ID from datasource 1 First_Name from datasource 1 Last_Name from datasource 1 ________________________________________________________________________________ Employee_ID from datasource 2 First_Name from datasource 2 Last_Name from datasource 2 --------------------------------------------------------------------------------- I need to create report (.rdl) with one table that contain the data from these two databases because when I do it in 2 tables (the second table is without header and next to the first table), when I do export to CSV, it create empty row between the tables and add headers to the second table even that I delete the header row/ did hide). and that effect the filtering on the result in the CSV file (I don't want to tell the client manually remove the empty row and the header). I want to get the databases from the clients (in SQL Server Reporting Services into two different datasource) and that why in the custom query I don't want to use union or join and add the second database. at the end, it doesn't meter to me if I'm using two or one dataset files, and if it two or one tables in the reports, just need after I do export to CSV, that it will in the file in one table with no empty row or header repeated. Thank for helping. :)

Answer 1

So the table containing the FORMER employees no longer have the employee id #....

this is BAD practice! Recycling employee id# even more scarey

copy them into a temporary table using an auto-incrementing counter in place of the

employee id

then when merged and sorted, you can tell the former employees from the real one by examining the

employee id field, because they will look something like this:

ex.

1 former employee 1

2 former employee 2

3 former employee 3

....

etc etc

98 former employee 98

99 former employee 99

100 former employee 100

E20120325-9876 real employee1

E20150711-4325 real employee 2

E20190603-8921 real employee 3

...

yes or no?