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

See my reply to Mark Breen coming up.  This is not a bulk load thing.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> 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
> 
> 
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
>