Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 31-03-2009
Member
 
Join Date: Mar 2009
Posts: 1
how to create sql temporary table for xml format using vb.net

how to create temporary table for sql server 2005 for xml format by using vb.net
Reply With Quote
  #2  
Old 31-03-2009
Member
 
Join Date: Oct 2008
Posts: 54
Re: how to create sql temporary table for xml format using vb.net

Temporary tables can be created like any table in SQL Server with a CREATE TABLE or SELECT..INTO statement. To make the table a local temporary table, you simply prefix the name with a (#). To make the table a global temporary table, prefix it with (##).

Code:
Create a local temporary table using CREATE TABLE
CREATE TABLE #myTempTable
(
  Field1 INT,
  Field2 VARCHAR(20)
)
Reply With Quote
  #3  
Old 31-03-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: how to create sql temporary table for xml format using vb.net

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

ocmdnh = New SqlCommand("exec sp_makenhtable", oconn)

ocmdnh.ExecuteNonQuery()

'open nhtable

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

Dim dsnhtable As New DataSet("nhtable")

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_schema.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
Reply With Quote
  #4  
Old 31-03-2009
Member
 
Join Date: Apr 2008
Posts: 193
Re: how to create sql temporary table for xml format using vb.net

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

Code:
ocmdnh = New SqlCommand("exec sp_makenhtable", oconn)

ocmdnh.ExecuteNonQuery()

open nhtable

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

Dim dsnhtable As New DataSet("nhtable")

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:

Code:
CREATE PROCEDURE sp_makenhtable AS
if exists (select * from information_schema.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:

Code:
Dim ocmd As SqlCommand

ocmd = New SqlCommand("exec sp_dropsp_buildbranchlistandtable", oconn)

ocmd.ExecuteNonQuery()

Dim creationstring As String

If glf_custstat = "" Then

creationstring = "CREATE PROCEDURE sp_buildbranchlist 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_buildbranchlist 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.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

ocmd = New SqlCommand("exec sp_buildbranchlist", oconn)

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "how to create sql temporary table for xml format using vb.net"
Thread Thread Starter Forum Replies Last Post
Unable to create the temporary folder. Error 183 when starting Distiller Delgado Windows Software 5 14-07-2010 12:16 PM
Writing temporary table problem Jabeena Software Development 4 13-02-2010 12:20 PM
How can I convert text data into table format? Ram Bharose Windows Software 7 30-12-2009 02:58 PM
Unable to create temporary folder jean-paul martell Operating Systems 2 25-05-2009 11:38 PM
Temporary table in VB.NET ArunJ Software Development 4 18-02-2009 07:20 PM


All times are GMT +5.5. The time now is 11:30 AM.