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

Insert tables from a dataset into a temp table

Hello,
I would like to insert brand new records, but I don't want to save it to a database, rather insert them into a temp table, run a stored procedure against it, and then clear the temp table out of memory.  Is that even possible?  Thanks.  

P.S.  I was thinking of using a global temp table, but I'm hesitant...
0
ispcorp
Asked:
ispcorp
  • 3
  • 2
  • 2
2 Solutions
 
davidlars99Commented:
you have to first create temporary table

CRATE TABLE #myTable (...)

and insert data

INSERT INTO #myTable (...) VALUES (...)

and then empty/truncate it

TRUNCATE TABLE #myTable


when you create tables with "#" sign, they autonaticaly are placed in temp database
0
 
b1xml2Commented:
CREATE PROC spGetData
AS
DECLARE @table TABLE (ID int Identity(1,1) not null, User varchar(10) null,Date datetime null)
insert @table
(User,Date)
select
      username as User,
      dob as Date
from
      mytable
      


'process table
'when the procedure ends, the table is cleared
      
0
 
ispcorpAuthor Commented:
I should have mentioned that I'm creating the temp table within asp.net

Will the table still exist even if its a separate transaction?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
b1xml2Commented:
ahh =) i c the light, why dont you have an actual table with GUID being the identifier of that request pipeline and you can manipulate data and then you can clear it up.... That way transactional and scoping issues are handled.
0
 
davidlars99Commented:
table will exist until you reboot the computer
0
 
ispcorpAuthor Commented:
Actually davidlars99...The table was cleared from memory as soon as the transaction ended.  There were two ways I could do it...Keep the transaction alive by not comitting it, or creat a "GLOBAL" temp table with "##" in front of its declaration, which would be kept on the temp database until you explicitly drop it.

I chose to create a global temp table with sessionid as part of its name, do my  processing with the stored procedure, and then drop it when I'm done with it.  

b1xml2 was right about, but I found a work around using GLOBAL temp tables.

Davidlars...Do you rebuke anything I just said?
0
 
davidlars99Commented:
no, I don't rebuke.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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