Lookup table without modifying database

Question

Lookup table without modifying database

Göran Roseen

SSC Enthusiast

Points: 179
More actions
April 28, 2023 at 1:03 pm

#4181059
I have a production database that I will be running queries against. I am not allowed to modify that database.
Then I have a lookup table in a local database on my computer with c:a 1000 rows.
I want to use the lookup table when querying the prod database. I do not expect this to turn into a recurring thing, otherwise we would have added the table.
What do you think would be the easiest and best way?
Thoughts that have crossed by mind are:
- outputting the table as text in the form of "select key, value from (VALUES (1, 'a' ), (2, 'b') ... )"
- somehow getting the data into a table-valued variable
- joining from the prod server to my computer (disqualified, since I can't add my computer as a linked server on Prod)

Viewing 14 posts - 1 through 13 (of 13 total)

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

dale_berta Ten Centuries Points: 1322 More actions · Answer 1

If this is a one-time thing I'd probably script something locally to generate what winds up as an INSERT to a table variable, then put that into the script you run when you query the server.

DECLARE @Lookup TABLE
(
 Id  INT
,Val NVARCHAR(30)
);
IF (1 > 0) BEGIN
INSERT INTO @Lookup
  VALUES (1, 'a')
        ,(2, 'b')
        ...;
END

There is a limit to how many rows you can do in an INSERT like this, but I keep mine smaller than what is allowed, maybe a few hundred rows. That makes it easier to find errors if something goes wrong. If a string value can have embedded apostrophes, don't forget to double them, as in 'O''Brien'.

I include whitespace for readability. Putting the otherwise unnecessary IF BEGIN END lets me collapse the entire thing in the editor and hide the 1000 or so lines of code that are there.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

If you use INSERT/SELECT FROM (VALUES)x(somecol1, somecoletc...), there appears to be no limit. I've done 50,000 rows as a one-off.

You could make a temporary Linked Server to your machine. You just need to give the login for the linked server a username and password.

Using a similar method, you could also use OPENROWSET or OPENDATASOURCE, as well (if enabled).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

For a small set of data I might use a table variable - but for any significant number of rows I would use a regular temp table.

Another option - you could use BCP (or any other tool) to export the data and import the data directly to a temp table instead of generating a script to insert the data.

You could also request a new database be added to production to host your lookup table(s). Talk to the DBA's and see if they would be willing to create that type of database - even if it means creating a process to update/manage the lookup table, having it on the production instance would be much easier than generating scripts, inserting data to temp tables and then running the final query.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

Just create a “permanent” table in tempdb. It will persist and be available like any other table, until it is explicitly dropped or or the SQL service is restarted.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

I would say... don't make it a permanent table in TempDB. It may be justification for them to commit homicide and hide the body.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 6

That’s the thing about tempdb, it’s true to it’s name. Nothing you put there is truly permanent. By using normal create commands in tempdb you don’t have to worry about session scopes.

The fact that tempdb is dropped and recreated anytime the SQL service restarts, means that you won’t accumulate permanent clutter if you forget to explicitly drop things and should eliminate the hassles & hoops of full blown sdlc needed to add objects to production user databases.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

As there are so few rows, I'd create a chunk of code which drops, creates and populates the table in TempDB

DROP TABLE If EXISTS #SomeLookup;

CREATE TABLE #SomeLookup(SomeKey PRIMARY KEY CLUSTERED, SomeValue);

INSERT #SomeLookup(SomeKey, SomeValue)
VALUES (,),(,), ..., (,)

and then just paste that code at the start of your queries.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 8

Jason A. Long wrote:

That’s the thing about tempdb, it’s true to it’s name. Nothing you put there is truly permanent. By using normal create commands in tempdb you don’t have to worry about session scopes. The fact that tempdb is dropped and recreated anytime the SQL service restarts, means that you won’t accumulate permanent clutter if you forget to explicitly drop things and should eliminate the hassles & hoops of full blown sdlc needed to add objects to production user databases.

You'll just have clutter in it until they do a reboot or you remove your stuff when done. And, remember, TempDB starts off in valuable memory.

If someone made a real table in TempDB at work, they'd need to sharpen up their resume skills. It's something that I won't tolerate. Use a scratch database for that kind of stuff. We have one and it automatically gets raked out each night. If you need something more permanent, then talk with the DBA.

As for avoiding the "hassles & hoops", that's a great way to flunk an audit. No one should put anything in the production server unless it has gone through the proper channels. The job you save may be your own.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Y.B. SSChampion Points: 11649 More actions · Answer 9

I think it really comes down to the actual intent going forward. If this is truly a query that will be run a couple of times then you have several options. Temp tables, table variables, and CTEs will all work just fine. I would just be a little concerned that this could turn into bad habits. What if this becomes a more common task for the OP? Then they might be inclined to keep the table in the tempdb all the time and build more code around that. Next thing you know things break every time SQL is restarted. After loading the table a few times manually, they get clever and load the table when the service restarts as a SQL Job. Then the technical debt just keeps piling up and up.

So for sure, if you are going to run this code a couple of times, use tempdb but make sure to drop the table when you are done. Otherwise, look to create a "companion" database on the server. While creating external dependencies sucks especially when you need to move those databases in the future. It's still often the best option if you don't have access to create objects in the database or they belong to 3rd party vendors.

SELECT quote FROM brain WHERE original = 1
0 rows returned

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 10

Another option if you don't want to be moving data between your local and the prod server, creating temp tables, CTE's etc... would be to use SSIS and just do the lookup in flight.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 11

ZZartin wrote:

Another option if you don't want to be moving data between your local and the prod server, creating temp tables, CTE's etc... would be to use SSIS and just do the lookup in flight.

which would then likely be a break in licensing terms as the SQL instance on the OP pc is likely not licensed for production usage (would need to be Express or one of the paid editions).

even using the lookup tables to extract data to a file and use that file as part of a SQL script is breaking those licensing terms.

This reply was modified 1 year, 6 months ago by frederico_fonseca.

Göran Roseen SSC Enthusiast Points: 179 More actions · Answer 12

Thanks all for your valuable insights!

I am a "do it by the book" type of guy, so I don't expect an ugly solution to become permanent. If it becomes permanent, I know my IT department will gladly add the table in the DB for me.

I will probably follow Jeff's advice not to use TempDB, even though I think I would still get to keep my job if I did. It would have had the benefit of making it possible to use "Import data" in SSMS instead of formatting the data into VALUES clauses.

I just think a table valued variable is more elegant. Not in itself, but the fact that I have a set of queries that need it, and I can keep the table data and the queries together. If I ever need the queries again (not earlier than next year) I will also have the lookup table there.

Bonus points to dale_berta for the trick with an IF-BEGIN-END to be able to collapse the code containing the values! 🙂

Phil Parkin SSC Guru Points: 246960 More actions · Answer 13

Göran Roseen wrote:

I will probably follow Jeff's advice not to use TempDB, even though I think I would still get to keep my job if I did. It would have had the benefit of making it possible to use "Import data" in SSMS instead of formatting the data into VALUES clauses.

I'm sure that Jeff will chime in here if I am not correct, but I do not believe that his advice was to completely avoid using TempDB at all costs. Instead, he suggests not relying on any sort of permanence for objects within it.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin