dotnet distributed apps: Bulk Insert From Dataset -- dotnet distributed apps

Bulk Insert From Dataset

Dianna
11/27/2004 4:01:01 PM

dotnet distributed apps:

I have a datafeed coming from another Database which I am populating into a
Dataset. I want to take the dataset and insert all the rows into a Sql
Table. So far I have an 'insert stored proc' and I pass all the parameters
from .Net and execute (1 row at a time). However, this is tedious. This
data feed contains about 100 columns (so I have to pass 100 parameters to the
Stored Proc). It is a straight insert (the columns in the datatable are the
same as the columns in the sql table and no identity column).

Is there an easier way to do this? In the old days I would have just used a
BCP. However, this app uses a Data Access Layer and we want to put all our
data access functions here.

I have been fooling around with the Data Adapter, but it seems that I will
have to still pass all the parameters to the insert stored proc.

Any help would be great.
Thanks, Dianna

Re: Bulk Insert From Dataset

Shayne Smyth
12/3/2004 6:57:39 PM

Hi
I had the same problem.

I am now using
DataSet().GetXML() into a single NTEXT SQL Parameter

StoredProc
CREATE PROCEDURE dbo.usp_UpdateXML (@XMLTEXT NTEXT)
AS
DECLARE @HANDLEXML INT, @XPATH VARCHAR(50), @NS VARCHAR(100)
SET @XPATH = '/NewDataSet/Table1'
EXEC SP_XML_PREPAREDOCUMENT @HANDLEXML OUTPUT, @XMLTEXT

INSERT INTO WHATEVER (ID, AAA,BBB)
SELECT id,aaa,bbb
FROM OPENXML(@HANDLEXML, @XPATH, 2)
WITH ( id INT, aaa INT, bbb VARCHAR(100))

--Release handle obtained on xml document
EXEC SP_XML_REMOVEDOCUMENT @HANDLEXML

[quoted text, click to view]