Solved

HOW CAN I CREATE TEMPORARY TABLE

Posted on 2011-02-25
13
631 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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