Solved

HOW CAN I CREATE TEMPORARY TABLE

Posted on 2011-02-25
13
629 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
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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