1

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

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
MarcGel
  • 299
  • 6
  • 20

1 Answers1

2

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.
PSK
  • 17,547
  • 5
  • 32
  • 43
  • 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 '18 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 '18 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 '18 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 '18 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 '18 at 18:29
  • You are not removing any column, you are joining two tables based on same code. Record which the join will return will have same code. In view you are just removing the redundant code. – PSK Mar 14 '18 at 18:29
  • Sorry, I see now where PSK made a change I didn't notice. Let me try it again. – MarcGel Mar 14 '18 at 18:29