• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

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

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
shyamaladevib
Asked:
shyamaladevib
3 Solutions
 
aelliso3Commented:
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
 
vdr1620Commented:
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
 
Lara FEACommented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now