?
Solved

best procedure? - select A,B,C into #TABLE or insert dbo.table1 select M,N,O from #TABLE

Posted on 2010-08-23
3
Medium Priority
?
536 Views
Last Modified: 2012-05-10
Whcih is the best way to do?
select into or insert?
if there is a suggested best way, does it differ if we are using temporary and normal tables?
Thanks,
Shyamala
0
Comment
Question by:shyamaladevib
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Assisted Solution

by:aelliso3
aelliso3 earned 332 total points
ID: 33505537
If you create a table and use INSERT, then you get to pick the datatype, if you use SELECT INTO, then SQL will choose it. Also, creating the table beforehand will give you the option to do extra things like create indexes, etc.
I personally stick to INSERT INTO
On the second question, if you only need it for that query (or sp) then just use a temp table. It will place the table into the tempdb instead of taking up space on the database you are db file your production databases are on. Only create a permanent table if you are going to be using it after your task is completed.
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 336 total points
ID: 33507392
Since you are using SQL 2008... I would suggest you also consider using Table Variables and CTE's ..Which have various advantages over Temp tables..
Note : Each of these have their own Limitations.. Use which ever suits your scenario

These links might help
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx
0
 
LVL 11

Assisted Solution

by:Larissa T
Larissa T earned 332 total points
ID: 33507599
Depends on what you need
select A,B,C into table from .....
Saves your time since you don't need to type "Create table ..."
Useful when your result is query of several tables or wide tables (a lots of column ) and you want  need table  for future use. If needed you can later script table from object browser and do modification on create

Using "insert into table: first require you to create table. But you do have full control over table layout.

tableName - permanent table and you will need explicitly drop it when not needed
#table only exist during current connection. no need to drop when you close your query or finish proc
Comparison in terms of performance to table variables
http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx




0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question