Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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
0
a b
Asked:
a b
  • 4
  • 2
  • 2
  • +1
1 Solution
 
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
0
 
a bAuthor Commented:
thanks e1v, but i don't think that works with access.

0
 
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
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
0
 
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.

0
 
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.

0
 
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();
            }
      }
}
0
 
a bAuthor Commented:
looks good c_myers, i'll try it tomorrow.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now