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
Thanks
ASKER
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?
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?
ASKER
ok if I create the table and clear it each time, how would I then go about getting the dataset into the table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/E
WITH (...)
exec sp_xml_removedocument @xmlDocReturn
for more documentation
http://msdn.microsoft.com/library/d..._oa-oz_5c89.asp
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/E
WITH (...)
exec sp_xml_removedocument @xmlDocReturn
for more documentation
http://msdn.microsoft.com/library/d..._oa-oz_5c89.asp
use xmlspy