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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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