How can I lookup a list of values in SQL?

1

Hello

I have a list of Client IDs and I want to lookup these IDs in an SQL table and extract some other columns related to these IDs.

However, I am not allowed to write anything in the SQL server.

How can I do this?

Thanks!

2

what is 'some other columns'?? Why can't you run any sql? Do you have a local copy? can you at least provide a sample of what you'd like to return?

3

The question is not what I would like to return. The question is how to lookup a long list or in other words, how can I join the SQL table of the server with the long list of values I want to look up, if I cannot write anything on the server.

4

extract from SQL Server to Excel file and then do your stuff

or

bcp command
-- this you can execute sql file from command line

5

if by "I am not allowed to write" you mean you are not allowed to persist artifacts such as stored procedures on the server, then you can just do the following and save the file locally

declare @clientIds table(clientid int)
insert into @clientIds
select 1 union
select 2


select * 
from orders o 
join @clientIds ci on o.clientId = ci.clientid
6

What tool are you using to access SQL Server? If you are using SSMS then you absolutely have the ability to create temporary objects - as needed.

You can easily take the list of client ID's and put that into an Excel spreadsheet. Then - in that spreadsheet create a computed column that formats the client ID (if alphanumeric then wrap in single-quotes) - then either prepend or postpend a comma...for example:

, 123456
, 123232
, 122121

Copy/Paste the list of ID's into a query window with the following:

DROP TABLE IF EXISTS #myTempTable;

CREATE TABLE #myTempTable (ClientID int);  -- or varchar with the appropriate size
INSERT INTO #myTempTable (ClientID)
VALUES ( *** insert your list of clientIDs here ***  );

Remove the very first comma...now you have a temp table with the list of client ID's. You can then use that temp table in your query.

1 Like