copy table from access to sql server

hello experts!

i'm looking for the simplest piece of code that will copy a whole table from an access database to a sqlserver database.

the sqlserver database is empty, there are no constraints, relationships or primary keys.

the structure of both tables is identical (or as identical as can be)

i'm assuming there is a couple of lines of code that will do it, presumably using datatables, dataadapters etc.

i'm using system.data.common for all data objects.


thanks very much

gavin
LVL 2
a bAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

e1vCommented:
If you're using net 2.0 you can use SqlBulkCopy when the table-definitions are identical
Here is a link to an example: http://davidhayden.com/blog/dave/archive/2006/03/08/2877.aspx
a bAuthor Commented:
thanks e1v, but i don't think that works with access.

AGBrownCommented:
You'll basically need to define your select statement for the access database, and then execute individual insert statements for each row that comes out of the select. I would do it using the System.Data.SqlClient for the sql objects, but I can't call that based on performance, just what I am used to.

Have you considered using the SQL import wizard (assuming you are using sql server 2000) to create a package that you could then execute using c#? This would probably be a lot faster than using c# to do the transfer.

Andy
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

a bAuthor Commented:
i'm trying to write code in the most generic way possible. i'm copying the structure of each table in the source database to the target database, then copying the data.

i presumed that there would be a way of just filling a dataset (or datatable) using one dataadapter, calling SetAdded on the rows, then updating it using another.

maybe not.

thanks anyway for your answers.
AGBrownCommented:
I think you are going to have to specify, at the minimum, the SELECT command for each table. For this to work there must be a key column in the table though. The SqlCommandBuilder can then generate the appropriate INSERT statements, and can be used in conjunction with the  dataadapter objects.

Are you using .NET 1.1 or 2.0?

Andy
c_myersCommented:
Do you have to do this in code? I'd say the easiest way of doing this is with SQL Enterprise Manager's Import Data or, if you're using SQL 2005, then SQL Management Studio's Import Data stuff.

a bAuthor Commented:
this has to be done in code

i'm using .net 2.0

i have an array of table names, a DbConnection for the existing db, a DbConnection for the new db, both valid

the tables are identical in both dbs.

i can enforce a primary key on a column if necessary.

i can make the names and types of the columns available if neccessary.

i'd like to copy all rows from each table in the existing db to the corresponding table in the new db.

c_myersCommented:
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;

public class DbCopyTest
{
      private const int DB_BATCH_SIZE = 500;

      public static void Main( string[] args )
      {
            try
            {
                  CopyAccessDb();
            }
            catch( Exception ex )
            {
                  Console.WriteLine(ex);
            }
      }
      
      private static void CopyAccessDb()
      {
            string accessConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;OLE DB Services=0;Data Source=c:\\db1.mdb";
            string sqlConnectStr = "Server=.;Database=YourDb;Integrated Security=SSPI";

            string selectStmt = "SELECT val1, val2, val3, val4 FROM yourTable";
            string insertStmt = @"
INSERT INTO YourSQLTable (val1, val2, val3, val4)
  VALUES( @param{0}val1, @param{0}val2, @param{0}val3, @param{0}val4 )
";
            
            StringBuilder sqlBldr = new StringBuilder();
            
            int curItem = 0;
      
            using( OleDbConnection oleCon = new OleDbConnection( accessConnectStr ) )
            {
                  oleCon.Open();
                  
                 using( OleDbCommand oleCmd = new OleDbCommand(selectStmt, oleCon) )
                 {
                       using( OleDbDataReader rdr = oleCmd.ExecuteReader() )
                       {
                             SqlCommand curCmd = new SqlCommand();
                             try
                             {
                                   while( rdr.Read() )
                                   {
                                         if( curItem >= DB_BATCH_SIZE )
                                         {
                                               ExecuteCommand( sqlConnectStr, curCmd, sqlBldr.ToString() );
                                               curCmd = null;
                                               curCmd = new SqlCommand();
                                               sqlBldr = new StringBuilder();
                                               curItem = 0;
                                         }
                                         
                                         sqlBldr.AppendFormat(insertStmt, curItem);
                                         curCmd.Parameters.Add(string.Format("@param{0}val1", curItem), rdr[0]);
                                         curCmd.Parameters.Add(string.Format("@param{0}val2", curItem), rdr[1]);
                                         curCmd.Parameters.Add(string.Format("@param{0}val3", curItem), rdr[2]);
                                         curCmd.Parameters.Add(string.Format("@param{0}val4", curItem), rdr[3]);
                                         
                                         curItem++;
                                   }
                                   
                                   if( curCmd != null )
                                   {
                                         ExecuteCommand( sqlConnectStr, curCmd, sqlBldr.ToString() );
                                   }
                             }
                             finally
                             {
                                   if( curCmd != null )
                                         curCmd.Dispose();
                             }
                       }
                 }                 
            }
      
      }
      
      private static void ExecuteCommand( string connectStr, SqlCommand sqlCmd, string statements )
      {
            using( SqlConnection con = new SqlConnection(connectStr) )
            {
                  con.Open();
                  
                  sqlCmd.Connection = con;
                  sqlCmd.CommandText = statements;
                  sqlCmd.ExecuteNonQuery();
            }
      }
}

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
a bAuthor Commented:
looks good c_myers, i'll try it tomorrow.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.