Friday, October 10, 2008

Inserting Large Amount of Data - Bulk Insert

In order to pass a large amount of data, in VB.NET we can use Bulk Insert, here's the article for more information on the sample too.
Bulk insert sample in vbs file, click here.

If you need to create an XML from the Dataset, then u can click here.

Here's a sample on reading XML format in stored procedure:
DECLARE @idoc int

DECLARE @doc varchar(1000);

Select @doc ='
<ROOT>
<Customer CustomerID="1" ContactName="John 1"/>
<Customer CustomerID="2" ContactName="John 2"/>
<Customer CustomerID="1" ContactName="John 3"/>
<Customer CustomerID="2" ContactName="John 4"/>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

EXEC sp_xml_removedocument @idoc

Please take note that this is not reading from the physical XML file, this can only be used for reading table field with the value of XML formatted text.
More info on feeding XML to Stored Procedure, click here.

This is a sample of reading physical file from the stored procedure, but I have not tested it as I do not have this permission in my office development environment.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
I guess this action requires sysadmin rights.
declare @objFSys int
declare @objFile int
declare @blnEndOfFile int
declare @strLine varchar(4000)

exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out

-- Change the file path to the one that is passed to your stored procedure
exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, 'C:\test.txt', 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
while @blnEndOfFile=0 begin
exec sp_OAMethod @objFile, 'ReadLine', @strLine out
-- Here you got one line from the file
select @strLine
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
end
exec sp_OADestroy @objFile
exec sp_OADestroy @objFSys

No comments: