Solved

SQL : How to insert couple of Table records to a TempTable ?

Posted on 2011-02-15
6
572 Views
Last Modified: 2012-05-11
SQL 2008

How to insert couple of Table records to a TempTable ?

I have Table1, Table2.

I need to move both Tables to a Temp Table, in which i can do some calculation.



0
Comment
Question by:chokka
6 Comments
 
LVL 2

Expert Comment

by:eljakim
ID: 34901217
You can create a table as follows:

create tmptable select * from table1;

If you need to have several extra fields, an easy way to do this would be:

create tmptable select *, 1 as extra_field from table1;

When you want to get rid of the table:

drop table tmptable;
0
 

Author Comment

by:chokka
ID: 34901234
I need to insert two Tables to one Temp Table
0
 
LVL 2

Accepted Solution

by:
eljakim earned 167 total points
ID: 34901273
do they have the same structure?

create table tmptable select * from table1 union select * from table2;

If they don't have the same structure you have to specify the field names.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 167 total points
ID: 34901309
You can also prefix the table name with a # sign to create a local temporary table (for example, #TempTable).  They have better performance than regular tables.

More on temporary tables below:

http://msdn.microsoft.com/en-us/library/ms177399.aspx

Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.

There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 166 total points
ID: 34901324
you can put them into two seperate temp table

and why are you using this? is the calculation too complicated? maybe we can do it one query without need of temp table/cursors...

what are you trying to do actually?
0
 

Author Comment

by:chokka
ID: 34901341
I did it in this way
declare @TempTable table
( 
	NDC nvarchar(1000), 
	Qty nvarchar(1000) 
)

Insert Into @TempTable (NDC,Qty)
select		
			
			TR.NDC,   
			Sum(TR.[Dispensed Qty]) as Qty
from		TransactionReport TR    
Group By    TR.NDC

Insert Into @TempTable (NDC,Qty)
select BR.NDC,BR.Qty  from BalanceReport BR


select NDC,Sum(CONVERT(INT, Qty)) as Qty from  @TempTable
Group By NDC

Open in new window

0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Unable to Uninstall Visual Studio 2015 7 27
SSRS Enable Remote Errors 4 26
Help in Bulk Insert 9 35
SQL Syntax: How to force case sensitive query? 2 30
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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

803 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