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
480 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
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now