Combining columns from Multiple SQL databases into CSV

Welcome Forums General PowerShell Q&A Combining columns from Multiple SQL databases into CSV

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 7 years ago.

  • Author
  • #6375

    Points: 0
    Rank: Member

    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.

    Thank you in advance for your help.

    by RichardSiddaway at 2012-12-11 13:50:16

    I would turn this problem on its head :
    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

    by cmille19 at 2012-12-11 14:28:13

    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.

The topic ‘Combining columns from Multiple SQL databases into CSV’ is closed to new replies.