join two OPENQUERY results

Question

Post reply

join two OPENQUERY results

PHXHoward

SSCrazy Eights

Points: 8389
More actions
September 22, 2011 at 11:33 am

#257662

Good morning. Is it possible to join two OPENQUERY outputs together?
I'm using OPENQUERY with LDAP to pull out employee name and email address and manager number.
this is working fine:
DECLARE @TSQL varchar(8000), @VAR varchar(100)
SELECT @VAR = '15571'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(ADSI,''SELECT ManagerEmployeeNumber, mail,displayName
FROM ''''LDAP://dc=mycompany,dc=com'''' WHERE employeenumber= ''''' + @VAR + ''''''')'
EXEC (@TSQL)
it gives displayname, mail, and ManagerEmployeeNumber for the specified person.
I need to take that output and add the manager's name and email address. So basicly I think I need two queries joined where the manager employee number becomes the @VAR.
Is this possible using OPENQUERY or dynamic sql?
Thanks much for reading.
Hope to see some of you are SQL PASS.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

I would create a temp table and insert into it, then update it. Why would you choose to use dynamic SQL? 🙂

Thanks,

Jared

Jared
CE - Microsoft

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

Managed to do it by dumping the queries into temp tables and then selecting from there. Probably not the most efficient way to accomplish this.

create table #temp

(

displayName varchar(500),

mail varchar(100),

manageremployeenumber int

)

create table #temp1

(

displayName varchar(500),

mail varchar(100),

manageremployeenumber int

)

DECLARE @TSQL varchar(8000), @VAR varchar(100), @manager varchar(100)

SELECT @VAR = '12345'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(ADSI,''SELECT ManagerEmployeeNumber, mail,displayName

FROM ''''LDAP://dc=company,dc=com'''' WHERE employeenumber= ''''' + @VAR + ''''''')'

insert into #temp

EXEC (@TSQL)

select @manager =manageremployeenumber from #temp

SELECT @TSQL = 'SELECT * FROM OPENQUERY(ADSI,''SELECT ManagerEmployeeNumber, mail,displayName

FROM ''''LDAP://dc=company,dc=com'''' WHERE employeenumber= ''''' + @Manager + ''''''')'

insert into #temp1

EXEC (@TSQL)

select #temp.displayName as EmployeeName, #temp.mail as Email, #temp1.displayName as ManagerName, #temp1.mail as ManagerEmail from #temp, #temp1

drop table #temp

drop table #temp1

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 3

Thanks Jared.

The temp table approach is how I ended up doing it.

The DynamicSQL was from when I thought that the variable would have to be changed. Prob isn't necessary with the temp tables.

Jared SSC Guru Points: 61793 More actions · Answer 4

I still don't understand why you are using dynamic SQL. Just create 1 temp table and insert into it.

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 5

You can still pass the variable being that the open query syntax is executed as such. So, no need to store it again into a variable and execute it.

Thanks,

Jared

Jared
CE - Microsoft

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 6

Actually the dynamic sql is because I was not sure how to pass a variable to an OPENQUERY without structuring it first then executing it.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 7

Passing a variable into an OPENQUERY without dynamic SQL isn't working for me.

DECLARE @VAR varchar(100) = '12345'

SELECT * FROM OPENQUERY(ADSI,'SELECT ManagerEmployeeNumber, mail,displayName

FROM ''LDAP://dc=company,dc=com'' WHERE employeenumber= ''' + @VAR + '''' )

Incorrect syntax near '+'.

Jared SSC Guru Points: 61793 More actions · Answer 8

Crud... I forgot that we cannot pass variables to openquery in this way. Your original script was the only way to do it. I suppose, though, that you could still insert into only 1 temp table instead of 2 right?

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 9

I looked again at your code. I think you can just insert all into 1 temp table, and then join on itself to get your final outcome. Should be easier on the database engine.

Thanks,

Jared

Jared
CE - Microsoft

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 10

Yes, it can all go in one temp table.

Thanks for the suggestions. Appreciate your help.