by johnkeng at 2012-12-11 13:31:22
I’m looking for a way to compare and combine data from two separate SQL databases into a single CSV file. The first database contains all the employees in our organization and attributes for each employee, for example, Name, DeptName, DeptID, JobDesc, etc. The second database contains employees that are supervisors, managers, and VP’s. The database columns are DeptID, Name, and Accesstype. The DeptID and Name are the same in database so I’m sure I can use this to compare the data, but how can I add the Accesstype as an additional column in a new CSV file? My final step in all this is to use the information from the new CSV file to modify the Manager attribute in Active Directory.by RichardSiddaway at 2012-12-11 13:50:16
Thank you in advance for your help.
I would turn this problem on its head :by cmille19 at 2012-12-11 14:28:13
create a temporary database
create a temporary table from the data in database 1
create a second temporary table from data in database 2
index appropriately and write a SQL view to pull the data you want from the two tables
export as CSV
Even better - combine the two databases and its a single query
As a database guy I don’t see Powershell as being the best tool for the job at hand. You say you have two databases if they are on the same server you can easily join the data. If they are on separate servers it’s easy enough to setup a Linked Server or DBLink (depending on DBMS used). You would then use a regular SQL query to join the data. Most query tools like SQL Server Management Studio support saving a query result as a CSV file.by Infradeploy at 2012-12-19 02:13:20
If you hve to this on a day to day basis you need some kind of datawarehouse construction. For example a new (temporary) database linked to the 2, or a view with tables of both databases.
Solutions depending on the size of the databases and different autorisation and security issues.