Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

HOW CAN I CREATE TEMPORARY TABLE

Posted on 2011-02-25
13
Medium Priority
?
635 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

598 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