Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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
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.
                Thanks! For some reason it doesn't like that I'm using the column Code twice? "Column names in each view or function must be unique. Column name 'Code' in view or function 'vw_dept_manuals' is specified more than once"
– MarcGel
                Mar 14, 2018 at 18:18
                You don't need that twice, it should come once only as both will hold the same value. I have updated the view definition.
– PSK
                Mar 14, 2018 at 18:20
                @MarcGel are you trying to do this with select *? That would be one way to get that column twice....and is a horrible idea to do with a view.
– Sean Lange
                Mar 14, 2018 at 18:21
                Hmm, I'm not understanding? I have 2 tables and want to create a 3rd. Table1 has AD_Dept = Code, Table2 has Code = Manual. The codes in Table1 should reference the codes in Table2 to get the manual name. How do I remove one without the other? They are dependent.
– MarcGel
                Mar 14, 2018 at 18:25
                Huh? You are creating a view right? What columns do you want returned in your view? I have a feeling you are trying to use select *. That means you have two columns named Code which is invalid in a view.
– Sean Lange
                Mar 14, 2018 at 18:29
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.