• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

insert dataset as temp table in SQL

I have a dataset.  It could have 50 rows or 50,000 rows.  I want to create a temp table in SQL Server 2k that holds my data so that I can run a few stored procs on it and shoot out some summary data.  What I need to know is if there is a way to get the data from my dataset into a sql temptable.

Thanks
0
Fred Goodwin
Asked:
Fred Goodwin
  • 5
  • 2
1 Solution
 
AerosSagaCommented:
0
 
Fred GoodwinVP of Software DevelopmentAuthor Commented:
Are saying that you would put the data into XML and then try to create the table with XML?  Can you give more more detail on what you are thinking?
0
 
AerosSagaCommented:
well thats one way other wise just use maketable in sql to make the table and then update it with your dataset data.  Although it seems like a lot of wasted overhead to create this table each time.  Why not just create the table in your db and clear it each time before you add the new data?


0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Fred GoodwinVP of Software DevelopmentAuthor Commented:
ok if I create the table and clear it each time, how would I then go about getting the dataset into the table?
0
 
AerosSagaCommented:
Public Function CreateCmdsAndUpdate(myDataSet As DataSet, myConnection As String, mySelectQuery As String, myTableName As String) As DataSet
    Dim myConn As New OleDbConnection(myConnection)
    Dim myDataAdapter As New OleDbDataAdapter()
    myDataAdapter.SelectCommand = New OleDbCommand(mySelectQuery, myConn)
    Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(MyDataAdapter)

    myConn.Open()

    Dim custDS As DataSet = New DataSet
    myDataAdapter.Fill(custDS)

    ' Code to modify data in DataSet here

    myDataAdapter.Update(custDS, myTableName)

    myConn.Close()

    Return custDS
End Function 'SelectOleDbSrvRows

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondbdataadapterclassupdatetopic5.asp
0
 
AerosSagaCommented:
or use xml as previously suggested


IF you are using SQL Server

You can take the data in the dataset and write it to an XML string
http://msdn.microsoft.com/library/d...atasetasxml.asp

then pass that xml string to a stored procedure.
once you have the xml string you can use OPENXML to
insert your data

Ex

CREATE PROCEDURE spTest
@xmlData TEXT
AS
DECLARE
@xmlDocReturn INT

exec sp_xml_preparedocument @xmlDocReturn OUTPUT, @xmlData

INSERT INTO MyTable
(...)
SELECT
...
FROM OPENXML (@xmlDocReturn,'/ROOTTAG/ELEMENTTAG')
WITH (...)

exec sp_xml_removedocument @xmlDocReturn


for more documentation
http://msdn.microsoft.com/library/d..._oa-oz_5c89.asp
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now