cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stelles3000
New Member

Execute SQL Statement: variable from two different server into a sql query

Hello everyone, I am facing a problem.

 

I have two sql connections (two different server). I execute one query in the first connection and save the data in a variable (%QueryResult%). In the second connection I do another query but I want to put data from the first connection %QueryResult% into my sql query, but the result is always nothing.

Select
*
From
Tabel1
Where
List in (%QueryResult%)

 

QueryResult = Datatable :

 

Name111

Name112

Name113

Name114

 

When I do it directly in MS SQL Server Management Studio i would usually type in 

 

Select
*
From
Tabel1
Where
List in 

('Name111',

'Name112',

'Name113',

'Name114')



Do some have a solution?

2 REPLIES 2
Gene_Liang
Regular Visitor

Hi, I think the issue you faced was the conversion between variable types, can you click on the %QueryResult% in variable pane while the process was executed and post the screen capture so I can help you sort things out ?

shindomo
Continued Contributor
Continued Contributor

Hello @Stelles3000 

 

You have to convert from Datatable to List at first.

Use action "Retrieve data table column into list" to do so.

 

shindomo_1-1635576894505.png

 

Then use the action "Join text" to convert the list into a comma-separated string. Since we need to enclose each value in quotation marks, we need to set the custom delimiter to ','.

 

shindomo_0-1635578536643.png


Finally, pass a comma-separated string to the IN operator of the WHERE clause of the SQL statement. Don't forget to enclose variable in quotation marks.

SELECT * FROM [Employees]
WHERE [LastName] IN ('%JoinedText%');

 

shindomo_1-1635578610519.png

 

Best Regards.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,907)