sql-serveractive-directoryidentity-managementidentitymanager

SQL How to Create a View Using Cross Reference Tables


I am new to SQL but am pretty good with PowerShell and the AD Module. We are implementing MIM 2016 and I have to transpose my AD scripts into it. In one script I am pulling from AD users the Department attribute. Then, using 2 csv files, I do a cross reference code lookup to assign a corp manual to a user. For example, Finance gets code A which equals manual Revenue Integrity.

In MIM I think I need to do this cross reference in SQL and then flow the data into MIM and create a sync rule that maps Department to the right SQL manual based on the mapping. I was able to load the 2 tables in SQL using VS Integration Services and then from there I pulled together from examples on line, the cross referenced table with columns Code AD-Department Code Manual where the Dept codes match the Manual codes;

SELECT m.Code, m.[AD Departments],
mi.Code,mi.Manual
FROM Dept_Mappings AS m 
JOIN Manuals AS mi
ON m.Code=mi.Code

But now I'm not sure what to do? I think I could create a SQL View from this?If I wanted just the AD Dept and Manual columns in the view to use in MIM, how would I do that? Thx


Solution

  • Creating a view will be a good option. Views works as virtual table which you can re-use in different quires.

    To create view you need to write query like following.

    CREATE VIEW SomeView
    AS
    SELECT m.Code, m.[AD Departments],mi.Manual
    FROM Dept_Mappings AS m 
    JOIN Manuals AS mi
    ON m.Code=mi.Code
    

    If you just want to select two columns from the view, you can do it like following.

    SELECT [AD Departments],Manual
    FROM SomeView
    --You can put where condtion here if you want.