[dba-VB] What to do, what to do?

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Fri Nov 13 08:36:06 CST 2009


Hi John,

OK, if you're adding several millions rows then SqlBulkLoad can make your
"shot deal" in one shot :)

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, November 13, 2009 5:31 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] What to do, what to do?

In fact this is a one shot deal.  I need to do this one time, to one table.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi John --
> 
> You can also consider using SqlBulkLoad - below is just a simple sample,
and
> you can develop very flexible variations of this approach. SqlBulkLoad
> should be as quick as bcp maybe even quicker. In fact the source for
> SqlBulkLoad could be a SqlDataReader/IDataReader - if you manage this
> IDataReader to have all the data to bulkload then your programs will
become
> as streamlined as they probably have never been, FYI: IDataReader can be
> built in code with data sources being everything as e.g. .csv files etc.
> (and of course .mdb tables or .xls (ISAM) "tables" etc.etc.)
> 
> 
> using System;
> using System.Collections.Generic;
> using System.Text;
> 
> using System.Data;
> using System.Data.SqlClient;
> 
> namespace TestConsole.Samples
> {
>     public class SqlBulkLoadTest
>     {
>         public static void Run()
>         {
>             string connectionString = "Data Source=HAMBURG\\SQL2005;" +
>                 "Initial Catalog=Northwind;User Id=sa;Password=N/A";
>             using (SqlConnection connection =
>                    new SqlConnection(connectionString))
>             {
>                 connection.Open();
>                 string sql = "select * from [Shippers]";
>                 SqlCommand command = new SqlCommand(sql, connection);
>                 SqlDataAdapter adapter = new SqlDataAdapter(command);
> 
>                 DataSet dataSet = new DataSet("Shippers");
>                 adapter.Fill(dataSet);
> 
>                 Console.WriteLine("*** dataSet ***");
>                 foreach (DataRow dataRow in dataSet.Tables[0].Rows)
>                 {
>                     object[] items = { dataRow[0], dataRow[1], dataRow[2]
};
> 
>                     Console.WriteLine("{0} {1} {2}",
>                         dataRow[0], dataRow[1], dataRow[2]);
>                 }
> 
> 
>                 (new SqlCommand("delete from [NewShippers]",connection))
>                     .ExecuteNonQuery();     
>                 
>                 SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, 
>                                        SqlBulkCopyOptions.KeepIdentity);
>                 bulkCopy.DestinationTableName = "NewShippers";
>                 bulkCopy.WriteToServer(dataSet.Tables[0]);     
>             }
>         }
> 
>     }
> }
> 
> --
> Shamil
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, November 13, 2009 3:52 PM
> To: Discussion concerning Visual Basic and related programming issues.
> Subject: Re: [dba-VB] What to do, what to do?
> 
> Shamil,
> 
> That is precisely what I have in mind, with variations.  Then at the end,
> write all updates back to 
> SQL Server.
> 
> Thanks for the demo code, always helpful.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> <<< snip >>
>  
> 
> __________ Information from ESET NOD32 Antivirus, version of virus
signature
> database 4603 (20091113) __________
> 
> The message was checked by ESET NOD32 Antivirus.
> 
> http://www.esetnod32.ru
>  
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com


__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4603 (20091113) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru






More information about the dba-VB mailing list