Trying to break a bad habit

So, I have this habit where I copy tables into tempdb...basically, b/c it's easy to do SELECT * INTO tempdb..mytable.  For some reason, my boss would prefer me to DECLARE TABLE variables in my scripts instead (no one uses these except for me).  I'm reluctant b/c I don't want to spell out table definition every time.  Some of these tables have a lot of fields.  Anyway I can use DECLARE TABLE and not have to spell out the exact table definition?
MariaHaltAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Why don't you use a temporary table? As in replace tempdb..mytable with #mytable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
But if you are concerned about performance you would be wise to explore the advantages of variables of type table and or explicitly declaring temporary tables.
0
Evan CutlerVolunteer Chief Information OfficerCommented:
You could be right, and he could be as well.  
Here is a site that helps when deciding one over the other:
http://odetocode.com/code/365.aspx
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html


Another option, is to execute a global temp table...an idea that keeps a table in the schema, but truncates when connection is severed.

Last option is a physical table that you can use as a variable.  Table Definition permamently stays, but so does the instance.

Hope this helps.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Anthony PerkinsCommented:
>>You could be right, and he could be as well.  <<
But he is not using a temporary table, the author is using a table in the tempdb database.  Clearly not the same animal.
0
Evan CutlerVolunteer Chief Information OfficerCommented:
It depends on the scenario.  The author does not want to re-instantiate table dictionaries with every run.  That changes the game.  This is definately a philosophical discussion
0
Anthony PerkinsCommented:
>>This is definately a philosophical discussion <<
Actually not really I was pointing out that what the author is using is not a temporary table it is a table in the tempdb database.  Nothing philosophical about that.  By definition, SQL Server considers temporary tables all those tables named with a #(local) or ## (global).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.