ChatGPT Told Me How To Query A Remote SQL Server Given A Specific Need (But I Had To Ask A Few Times)

4 min readJun 23, 2023

Using SQL Server, I wanted to query a Remote Server (i.e Liked Server) with several possible custom values. I used ChatGPT for assistance. While it did provide the answer, it took a few tries.

Simple example to query multiple values NOT using a Remote Server

If it was only a few values, I would’ve done this:

SELECT * FROM myTable WHERE employeeID IN (1, 3, 9, 20)

If I had “a lot” of values, I would make a temporary table, something like this:

DROP TABLE IF EXISTS #temp

CREATE TABLE #temp (
  employeeId INT
);

INSERT INTO #temp (employeeId) VALUES (1), (3), (9), (20) . . . 
-- #temp would store several values (ex. 50)

SELECT * FROM myTable WHERE employeeID IN (SELECT * FROM #temp)

What I Wanted To Avoid

This query did work:

SELECT * FROM OPENQUERY(myConnection,
  'SELECT * FROM myTable WHERE employeeID IN (''1'', ''3'', ''9'', ... )

But I didn’t want to manually type out 50+ values in my IN statement. I wanted it to come from a table.

Yes, I do have keyboard shortcuts to do this quickly using SublimeText and/or Excel, but was looking for another way…

The Problem

This was my first query connecting to a Remote Server (one MS SQL Server to another), querying for several custom values:

Note this does NOT work

DROP TABLE IF EXISTS #temp

CREATE TABLE #temp (
  employeeId INT
);

INSERT INTO #temp (employeeId) VALUES (1), (3), . . . 
-- #temp would have a total of 50 values

SELECT * FROM OPENQUERY(myConnection,
  'SELECT * FROM myTable WHERE employeeID IN (SELECT * FROM #temp)')

ChatGPT Question 1

I asked why the above code didn’t work. The response was:

When using OPENQUERY to query data from a Linked Server in SQL Server, you cannot directly reference a temporary table from the local server. The reason is that temporary tables are specific to each session and are not accessible across different connections or servers.

It was then suggested that I create a new table on the Remote Server to store my values.

ChatGPT Question 2

I said I can’t make tables on the Remote Server. The response was to generate a comma-delineated list using SQL’s FOR XML PATH

I’ll spare you the code, because I got an error:

“The FOR XML clause is not allowed in an ASSIGNMENT statement”

ChatGPT Question 3

After an apology, the following code was created to create a comma-separated string inside of a loop:

DECLARE @ValuesList VARCHAR(MAX) = ''
DECLARE @Value VARCHAR(MAX)

DECLARE myCursor CURSOR FOR
SELECT [field] FROM #temp

OPEN myCursor
FETCH NEXT FROM myCursor INTO @Value

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ValuesList = @ValuesList + ',' + QUOTENAME(@Value, '''')
    FETCH NEXT FROM myCursor INTO @Value
END

CLOSE myCursor
DEALLOCATE myCursor

SET @ValuesList = STUFF(@ValuesList, 1, 1, '') -- Remove the leading comma

DECLARE @Query NVARCHAR(MAX)
SET @Query = 'SELECT * FROM table WHERE field IN (' + @ValuesList + ')'

SELECT *
FROM OPENQUERY(mySource, @Query)

ChatGPT Question 4

I popped that into my code, but got another:

Incorrect syntax near ‘@Query’

When I hover-over “@Query” in SQL Server Management Studio, it said “Expecting STRING, or TEXT_LEX”.

According to ChatGPT, the problem was that “It seems that the error is occurring due to the limitation of passing a dynamic query as a parameter to the OPENQUERY function”.

Here’s the final working code:

DECLARE @ValuesList VARCHAR(MAX) = ''
DECLARE @Value VARCHAR(MAX)

DECLARE myCursor CURSOR FOR
SELECT [field] FROM #temp

OPEN myCursor
FETCH NEXT FROM myCursor INTO @Value

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ValuesList = @ValuesList + ',' + QUOTENAME(@Value, '''')
    FETCH NEXT FROM myCursor INTO @Value
END

CLOSE myCursor
DEALLOCATE myCursor

SET @ValuesList = STUFF(@ValuesList, 1, 1, '') -- Remove the leading comma

DECLARE @Query NVARCHAR(MAX)
SET @Query = 'SELECT * FROM table WHERE field IN (' + @ValuesList + ')'

DECLARE @RemoteQuery NVARCHAR(MAX)
SET @RemoteQuery = 'SELECT * FROM OPENQUERY(mySource, ''' + REPLACE(@Query, '''', '''''') + ''')'

EXEC (@RemoteQuery)

Summary

ChatGPT is very impressive.
You may have to ask your questions multiple times to get exactly what you want.
This generated a lot of code and took a few tries. Had I been in a rush, I should have just used my original working solution without ChatGPT’s help (hard-coding comma-separated values in the query string).