[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=shms";
>             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
> 
>