Re: Bulk insert ArrayList into table

I agree with Dan. SqlBulkCopy is probably perfect for you described
(especially if the data is already in memory). You cna't directly use BULK
INSERT in T-SQL with a .NET ArrayList, but it would be a breeze with
SqlBulkCopy. I've used SqlBulkCopy heavily to load generated data from
memory into SQL tables, and have found it to be quite robust.

Linchi

"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OOmlDU35JHA.1096@xxxxxxxxxxxxxxxxxxxxxxx
Another option is the SqlBulkCopy class in .NET. You can pass a data
table or a DataRow array. There is no faster way to insert data into SQL
Server than a bulk insert technique.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Curious" <fir5tsight@xxxxxxxxx> wrote in message
news:7b7fa671-e6f6-461d-b2fb-ccc889305d42@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a C#.NET project that calls a stored procedure,
"algo_insert_swing", to insert records. Currently the stored procedure
takes a single record to insert one at a time.

CREATE PROCEDURE dbo.algo_insert_swing
(
@Cusip varchar(20),
@Ticker varchar(50),
@Type varchar(4),
@ConfirmationDate datetime,
@ExtremeDate datetime,
@ExtremePrice real,
@PercentageChange real,
@Duration int
)
AS

-- Insert a record
INSERT INTO dbo.algo_swings
(
sec_id,
ticker,
extreme_type,
confirmation,
extreme_date,
extreme_price,
percentage_change,
duration,
creation_time
)
VALUES
(
@Cusip,
@Ticker,
@Type,
@ConfirmationDate,
@ExtremeDate,
@ExtremePrice,
@PercentageChange,
@Duration,
GetDate()
)

GO

It's highly inefficient because there are so many records to insert.

I therefore, was thinking of collecting all of the records into an
ArrayList in C#.NET, and BULK INSERT the collection at once. However,
I don't know if this is possible, because I don't know if the stored
procedure can accept ArrayList as parameter and in what SQL syntax it
can INSERT the ArrayList into the table all at once.




.

Relevant Pages

  • Re: sqlBulkCopy and required permissions
    ... I believe the same permissions are needed for SqlBulkCopy as BULK INSERT. ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: laoding data from csv file
    ... I'm working with .net 1.1..so i can't use sqlbulkcopy concept.. ... You could use SQL Server's 'BULK INSERT' T-SQL command directly: ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv
    ... Bulk load: An unexpected end of file was encountered in the data file. ... Anyway, there was an error in the format file, at least in regards to ... This is probably when you need to look at SQL Server Integration Services, ...
    (microsoft.public.sqlserver.programming)
  • Re: can not bulk insert a network file onto a remote SQL server 2005
    ... SQL 2000 DTS can import the same file with no ... Bulk Insert task and when using the Import Column in a data flow task. ... There are clearly multiple people out here fighting it. ... > I get this error when run from Query analyser in remote machine ...
    (microsoft.public.sqlserver.dts)
  • Re: Use a sequence to bulk collect into a collection?
    ... Patrick Demets wrote: ... And this cursor returns one record. ... FETCH ew_eqmt_asgn_csr BULK COLLECT INTO ... SQL> create sequence testseq; ...
    (comp.databases.oracle.server)
Quantcast