[SOLVED] SQL How to Create a View Using Cross Reference Tables

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.