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
530 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 83 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 84 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 83 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 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