473,700 Members | 3,047 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating temporary tables at run-time in SQL using VB.Net

Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site
at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp

which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen
Nov 21 '05 #1
4 24190
Hi Coleen,

I do this sort of thing all the time, but generally I create tables that
persist. If I need to truncate them, I do so. Sometimes I just delete then
by calling one sp and then rebuild them with another.

Here's a simple example, creating the 'nhtable' table:
Dim ocmdnh As SqlCommand

ocmdnh = New SqlCommand("exe c sp_makenhtable" , oconn)

ocmdnh.ExecuteN onQuery()

'open nhtable

Dim danhtable As New SqlDataAdapter( "select * from nhtable", oconn)

Dim dsnhtable As New DataSet("nhtabl e")

danhtable.Fill( dsnhtable, "nhtable")

the sp sp_makenhtable deletes the table and then rebuilds it, and then fills
it with data; in sql server it looks like this:

CREATE PROCEDURE sp_makenhtable AS
if exists (select * from information_sch ema.tables where table_name =
'nhtable')
drop table nhtable
select imcacct, brname, addr, addr2, city, st, zip, draw, bundlect,
issuecode into nhtable from manifest where draw < 300
Sometimes the build is more sophisiticated, adding primary keys, indices,
etc. Sometimes I even delete and dynamically build the sp itself. I will
do this when the sp has variable needs that are not easily passed into the
sp itself. In that case, I will call it to create it and then call it again
to exec it:

Dim ocmd As SqlCommand

ocmd = New SqlCommand("exe c sp_dropsp_build branchlistandta ble", oconn)

ocmd.ExecuteNon Query()

Dim creationstring As String

If glf_custstat = "" Then

creationstring = "CREATE PROCEDURE sp_buildbranchl ist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring

Else

creationstring = "CREATE PROCEDURE sp_buildbranchl ist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring & " and custstat
= '" & glf_custstat & Chr(39)

End If

Dim sqladapt As New SqlDataAdapter

sqladapt.Select Command = New SqlCommand(crea tionstring, oconn)

Try

sqladapt.Select Command.Execute NonQuery()

Catch ex As Exception

MessageBox.Show (ex.Message)

End Try

ocmd = New SqlCommand("exe c sp_buildbranchl ist", oconn)

Try

ocmd.ExecuteNon Query()

Catch ex As Exception

MessageBox.Show (ex.Message)

End Try

Let me know if you have questions about these approaches; I'll be glad to
help.

HTH,

Bernie Yaeger

"Coleen" <co**********@y aho.com> wrote in message
news:e0******** ******@TK2MSFTN GP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp
which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen

Nov 21 '05 #2
Hi,

Here is some sample code on how to create a database, table, alter
table and
stored procedure.

Dim conn As SqlConnection

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load

Dim strConn As String

strConn = "Server = " & Environment.Mac hineName

strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(s trConn)

conn.Open()

CreateDataBase( )

CreateClientsTa ble()

End Sub

Private Sub CreateDataBase( )

Dim strSQL As String

strSQL = "if Exists (Select * From master..sysdata bases Where Name = 'VET')"

strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strS QL, conn)

cmd.CommandType = CommandType.Tex t

Try

cmd.ExecuteNonQ uery()

Catch

MessageBox.Show ("Error Creating DB")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub CreateClientsTa ble()

Me.Text = "Creating Clients Table..."

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobje cts " & _

"WHERE Name = 'Clients' " & _

"AND TYPE = 'u')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP TABLE VET.dbo.Clients " & vbCrLf & _

"END" & vbCrLf & _

"CREATE TABLE Clients (" & _

"ID Int NOT NULL," & _

"LastName NVarChar(20) NOT NULL," & _

"FirstName NVarChar(20) NOT NULL," & _

"Address NVarChar(150) NOT NULL," & _

"City NVarChar(20) NOT NULL," & _

"ZipCode NVarChar(5) NOT NULL," & _

"PhoneNumbe r NVarChar(20) NOT NULL," & _

"WorkNumber NVarChar(20)," & _

"CellNumber NVarChar(20)," & _

"Email NVarChar(50) NOT NULL," & _

"Balance Money NOT NULL," & _

"BalanceDat e DateTime NOT NULL," & _

"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _

"(ID))"

Dim cmd As New SqlCommand(strS QL, conn)

cmd.CommandType = CommandType.Tex t

Try

cmd.ExecuteNonQ uery()

Catch ex As SqlException

MessageBox.Show (ex.ToString, "Clients")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub MakeClientStore dProcedure()

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobje cts " & _

"WHERE Name = 'ClientInfo' " & _

"AND TYPE = 'p')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP PROCEDURE ClientInfo" & vbCrLf & _

"END"

Dim cmd As New SqlCommand(strS QL, conn)

cmd.CommandType = CommandType.Tex t

Try

cmd.ExecuteNonQ uery()

cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _

"@ClientID int " & vbCrLf & _

"AS Select * " & vbCrLf & _

"FROM VET.dbo.Clients Where ID = @ClientID"

cmd.ExecuteNonQ uery()

Catch ex As SqlException

MessageBox.Show (ex.ToString, "Error Creating Stored Procedure")

Finally

cmd.Dispose()

End Try

End Sub

Alter table example
strSql = "ALTER TABLE PetInfo ADD Vet int NULL"

cmdUpdate = New SqlCommand(strS ql, connVet)

connVet.Open()

cmdUpdate.Execu teNonQuery()

connVet.Close()

Ken

-----------------------------

"Coleen" <co**********@y aho.com> wrote in message
news:e0******** ******@TK2MSFTN GP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site
at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp

which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen

Nov 21 '05 #3
Bernie, it's because of kind people like you & Cor (not to mention the
countless other who've helped me) that I appreciate this Newsgroup so much.
I guess you could say I'm a good Analyst/Designer, but when it comes to
writing code, I need examples, and I appreciate so much the help that I get!
THANKS!

"Bernie Yaeger" <be*****@cherwe llinc.com> wrote in message
news:ep******** ******@TK2MSFTN GP11.phx.gbl...
Hi Coleen,

I do this sort of thing all the time, but generally I create tables that
persist. If I need to truncate them, I do so. Sometimes I just delete then by calling one sp and then rebuild them with another.

Here's a simple example, creating the 'nhtable' table:
Dim ocmdnh As SqlCommand

ocmdnh = New SqlCommand("exe c sp_makenhtable" , oconn)

ocmdnh.ExecuteN onQuery()

'open nhtable

Dim danhtable As New SqlDataAdapter( "select * from nhtable", oconn)

Dim dsnhtable As New DataSet("nhtabl e")

danhtable.Fill( dsnhtable, "nhtable")

the sp sp_makenhtable deletes the table and then rebuilds it, and then fills it with data; in sql server it looks like this:

CREATE PROCEDURE sp_makenhtable AS
if exists (select * from information_sch ema.tables where table_name =
'nhtable')
drop table nhtable
select imcacct, brname, addr, addr2, city, st, zip, draw, bundlect,
issuecode into nhtable from manifest where draw < 300
Sometimes the build is more sophisiticated, adding primary keys, indices,
etc. Sometimes I even delete and dynamically build the sp itself. I will
do this when the sp has variable needs that are not easily passed into the
sp itself. In that case, I will call it to create it and then call it again to exec it:

Dim ocmd As SqlCommand

ocmd = New SqlCommand("exe c sp_dropsp_build branchlistandta ble", oconn)

ocmd.ExecuteNon Query()

Dim creationstring As String

If glf_custstat = "" Then

creationstring = "CREATE PROCEDURE sp_buildbranchl ist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring

Else

creationstring = "CREATE PROCEDURE sp_buildbranchl ist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring & " and custstat = '" & glf_custstat & Chr(39)

End If

Dim sqladapt As New SqlDataAdapter

sqladapt.Select Command = New SqlCommand(crea tionstring, oconn)

Try

sqladapt.Select Command.Execute NonQuery()

Catch ex As Exception

MessageBox.Show (ex.Message)

End Try

ocmd = New SqlCommand("exe c sp_buildbranchl ist", oconn)

Try

ocmd.ExecuteNon Query()

Catch ex As Exception

MessageBox.Show (ex.Message)

End Try

Let me know if you have questions about these approaches; I'll be glad to
help.

HTH,

Bernie Yaeger

"Coleen" <co**********@y aho.com> wrote in message
news:e0******** ******@TK2MSFTN GP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to create a temporary SQL table using VB.net? I've checked the Microsoft

site
at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp

which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and read and read but still not "Get" it.... I need an example to go by, and then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen


Nov 21 '05 #4
Again, Thank you, thank you for all the help I've been getting!

Coleen :-)

"Ken Tucker [MVP]" <vb***@bellsout h.net> wrote in message
news:uN******** ********@TK2MSF TNGP15.phx.gbl. ..
Hi,

Here is some sample code on how to create a database, table, alter
table and
stored procedure.

Dim conn As SqlConnection

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load

Dim strConn As String

strConn = "Server = " & Environment.Mac hineName

strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(s trConn)

conn.Open()

CreateDataBase( )

CreateClientsTa ble()

End Sub

Private Sub CreateDataBase( )

Dim strSQL As String

strSQL = "if Exists (Select * From master..sysdata bases Where Name = 'VET')"
strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strS QL, conn)

cmd.CommandType = CommandType.Tex t

Try

cmd.ExecuteNonQ uery()

Catch

MessageBox.Show ("Error Creating DB")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub CreateClientsTa ble()

Me.Text = "Creating Clients Table..."

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobje cts " & _

"WHERE Name = 'Clients' " & _

"AND TYPE = 'u')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP TABLE VET.dbo.Clients " & vbCrLf & _

"END" & vbCrLf & _

"CREATE TABLE Clients (" & _

"ID Int NOT NULL," & _

"LastName NVarChar(20) NOT NULL," & _

"FirstName NVarChar(20) NOT NULL," & _

"Address NVarChar(150) NOT NULL," & _

"City NVarChar(20) NOT NULL," & _

"ZipCode NVarChar(5) NOT NULL," & _

"PhoneNumbe r NVarChar(20) NOT NULL," & _

"WorkNumber NVarChar(20)," & _

"CellNumber NVarChar(20)," & _

"Email NVarChar(50) NOT NULL," & _

"Balance Money NOT NULL," & _

"BalanceDat e DateTime NOT NULL," & _

"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _

"(ID))"

Dim cmd As New SqlCommand(strS QL, conn)

cmd.CommandType = CommandType.Tex t

Try

cmd.ExecuteNonQ uery()

Catch ex As SqlException

MessageBox.Show (ex.ToString, "Clients")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub MakeClientStore dProcedure()

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobje cts " & _

"WHERE Name = 'ClientInfo' " & _

"AND TYPE = 'p')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP PROCEDURE ClientInfo" & vbCrLf & _

"END"

Dim cmd As New SqlCommand(strS QL, conn)

cmd.CommandType = CommandType.Tex t

Try

cmd.ExecuteNonQ uery()

cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _

"@ClientID int " & vbCrLf & _

"AS Select * " & vbCrLf & _

"FROM VET.dbo.Clients Where ID = @ClientID"

cmd.ExecuteNonQ uery()

Catch ex As SqlException

MessageBox.Show (ex.ToString, "Error Creating Stored Procedure")

Finally

cmd.Dispose()

End Try

End Sub

Alter table example
strSql = "ALTER TABLE PetInfo ADD Vet int NULL"

cmdUpdate = New SqlCommand(strS ql, connVet)

connVet.Open()

cmdUpdate.Execu teNonQuery()

connVet.Close()

Ken

-----------------------------

"Coleen" <co**********@y aho.com> wrote in message
news:e0******** ******@TK2MSFTN GP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp
which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen

Nov 21 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2082
by: Soefara | last post by:
Dear Sirs, I have been developing an application on Windows with MySQL 3.23, making use of temporary tables. Now when I try to port the application to a Unix box running also MySQL 3.23, I keep running into "access denied" errors with the queries which employ temporary tables. This only happens with CREATE TEMPORARY TABLE type queries, all other queries work fine.
1
2267
by: Chetanwarade | last post by:
Hi, I have one problem? I want to know if I have to show all records from table on ASP page.Should I use temporary table? Also where to use temporary tables? Thank you
30
9838
by: btober | last post by:
Whenever I create a temporary table, with something like CREATE TEMPORARY TABLE temptable1 AS SELECT * FROM paid.ad_hoc_query; New schemas appear, with names like "pg_temp_1". I guess the appearance of these schemas with "temp" in the name indicates that they are "temporary" schemas and related to the temporary table creation, but the schemas persist even after the end of the session in which the temporary table was created.
2
3363
by: David Parker | last post by:
In our application we have a table that tracks network sessions. The usage is: 1) create a session record 2) read/update the record several times during the session 3) delete the session record when the session ends Update activity on this table is going to be pretty intense, and the transient nature of the data makes it a good candidate for a temporary and/or in-memory table.
12
3092
by: Joachim Pense | last post by:
Is there recommended way to get the execution plan for queries involving global temporary tables (from the UNIX command line or within a script)? I run the queries in Perl scripts, and the only way that comes to my mind is creating permanent tables that look like the temporary ones and run test versions of the scripts that use the permanent tables; then I can let the script stop wherever I want and start db2expln. But this way looks a...
1
2775
by: phil | last post by:
I have been developing some queries which will list the dependencies of Stored Procedures (this is with DB2/UDB v8). In doing so, I have noticed that dependencies are ignored if they occur in an insert into a temporary table. For instance, if you have the following procedure: CREATE PROCEDURE DBA.TEST BEGIN INSERT INTO SESSION.TEMP1 SELECT COUNT(*) FROM DBA.DEPARTMENT; END Then DBA.DEPARTMENT will not appear in the list of dependent...
0
1462
by: msbhaskar | last post by:
I got to know that we can create indexes on declared global temporary tables in DB2, but not on the created temporary tables. For my project, i need urgent inputs on this to how-to achieve this in DB2 by creating indexes on temporary tables? Pointers to any extend is helpful for me. Thanks in Advance. Regards, Bhaskar
4
9843
by: john | last post by:
When leaving the main menu form I want to delete some temporary tables. The following code on the form's close works: DoCmd.DeleteObject acTable, "tmpEnvelop". But is it possible to change it so that it deletes all tables starting with tmp? Thanks, john
1
1965
by: popwhub | last post by:
I am having problems printing some reports from my application that are generated from temporary tables. The application has an Access FE and a SQL Server BE. The current method uses a temporary workspace. This workspace uses ODBCDirect which bypasses the JET engine completely. However, these objects are temporary and are destroyed at the end of the function. In order to get the reports working I launch the reports before the object is...
10
6530
by: w.l.fischer | last post by:
Is it possible to have statistics on temporary tables? I frequently put 10000 or more rows in a temporary table and would like to know it the queries become faster with statistics on those tables.
0
8639
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9059
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8912
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7795
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6556
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5897
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4396
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4650
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3082
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.