Solved

HOW CAN I CREATE TEMPORARY TABLE

Posted on 2011-02-25
13
626 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
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
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
Comment Utility
 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
Comment Utility
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
 
LVL 5

Expert Comment

by:puffdaddy411
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:b001
Comment Utility
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
Comment Utility
@b001: Have you tried my above example?
0
 

Author Comment

by:b001
Comment Utility
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
Comment Utility
@b001: What database name and version are you using?
0
 

Author Comment

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

Expert Comment

by:Mohamed Abowarda
Comment Utility
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
regex to allow alphanumeric characters and \ 2 21
Need to Modify a Script I found 5 75
Hovering effect 9 28
XML & .net 5 16
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now