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:
Code:
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
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("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
Let me know if you have questions about these approaches; I'll be glad to
help.
Bookmarks