?
Solved

HOW CAN I CREATE TEMPORARY TABLE

Posted on 2011-02-25
13
Medium Priority
?
634 Views
Last Modified: 2012-05-11
Hi Experts
Please explain how to create temporary SQL table and drop it when not required.
Thanks
0
Comment
Question by:b001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34982975
Just prefix the table name with #.  The table will be dropped automatically when the session is closed, or the code executes a 'Drop Table'.

Here is an example:
Create Table #MyTable (col1 int, col2 nvarchar(50))

Drop Table #MyTable
0
 

Author Comment

by:b001
ID: 34983040
 HI wdosanjos:
when I add the follwoing to my VISUAL STUDIO code
Create Table #MyTable (col1 int, col2 nvarchar(50))


it ceates errors like create not declared , table not declared
as if you can not add SQl command in Visual Studio code.
Please help
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34983491
It would be something like this:

Dim cn As New SqlConnection(" << your connection string >> ")

cn.Open()

Dim cmd As SqlCommand = New SqlCommand()

cmd.Connection = cn

cmd.CommandText = "Create Table #MyTable (col1 int, col2 nvarchar(20))"
cmd.ExecuteNonQuery()

' Execute your code

cmd.CommandText = "Drop Table #MyTable"
cmd.ExecuteNonQuery()

cn.Close()

Open in new window

0
Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

 
LVL 5

Expert Comment

by:puffdaddy411
ID: 34983555
Dim TempTable as New System.Data.DataTable


Use that "TempTable" object to do whatever you need in memory.  If you want to load data into it from a SQL datasource or SQL Server query, use the System.Data.SqlClient.SqlDataAdapter object.
0
 

Author Comment

by:b001
ID: 34986819
Hi puffdaddy411:

I have dataset DS filled with data form table tablename
   Fmsadapter.Fill(dS, tableName)
I would like to insert this data to a temporary table
Please write the code for me

Thanks
0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 34986822
Try to use DECLARE:
DECLARE @TibetanYaks TABLE (YakID int, YakName char(30))

Open in new window

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT 	YakID, YakName
FROM 	dbo.Yaks
WHERE 	YakType = 'Tibetan'

Open in new window

UPDATE 	@TibetanYaks
SET 	YakName = UPPER(YakName)

Open in new window

SELECT *
FROM @TibetanYaks

Open in new window

0
 

Author Comment

by:b001
ID: 34987538
hi wdosanjos:
I used your code to create temporary Sql Table

cmd.CommandText = "Create Table #MyTable (col1 int, col2 nvarchar(20))"
cmd.ExecuteNonQuery()

Sometime it causes problem if that table was not droped.
So how can check if oblect already exist before creating it again.
Thanks
0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 34987699
@b001: Have you tried my above example?
0
 

Author Comment

by:b001
ID: 34987987
Hi Medo3337:

I am sorry , I couldn't follow your code, as soon as enter

 DECLARE @TibetanYaks TABLE (YakID int, YakName char(30))

error : statement is no valid inside method
I am using Visual Studio.
Please tell me where I am going wrong. I know very little about programming.

Thanks
0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 34988004
@b001: What database name and version are you using?
0
 

Author Comment

by:b001
ID: 34988100
I am using SQL server 2005 and runing Visual Studio 2008
0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 34988139
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 34992085
Here is the updated sample that checks if the table exists prior to creating it:

Dim cn As New SqlConnection(" << your connection string >> ")

cn.Open()

Dim cmd As SqlCommand = New SqlCommand()

cmd.Connection = cn

cmd.CommandText = "If object_id('tempdb..#MyTable') is not null Drop Table #MyTable"
cmd.ExecuteNonQuery()

cmd.CommandText = "Create Table #MyTable (col1 int, col2 nvarchar(20))"
cmd.ExecuteNonQuery()

' Execute your code

cmd.CommandText = "Drop Table #MyTable"
cmd.ExecuteNonQuery()

cn.Close()

Open in new window

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

743 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