Re: VS2003 transfer data between two data sources

On Sun, 9 Apr 2006 13:23:01 -0700, AbeR <AbeR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I am loading a DataSet from an access file (exported from another app that a
vendor provided) and want to appendthe data into an SQL Table that has the
same structure.

I can easily append rows to the SQL table, but that can't be a best
practice! Does anyone have any guidance as to taking the filled dataset from
excel and then doing an append to the SQL table without having to step
through all the rows (obviously the two have the same structure)

Currently:

private void TransferData()

string _eConnectionString;
// Connection string for the Excel file selected through UI into TextBox
fileName
_eConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
this.fileName.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [CIPEXPORT$]", oConn);

DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;

da.Fill(ds,"testData");
DataTable dt = new DataTable();

// Open up the SQL Server Connection
SqlConnectionConnection sConn = new SqlConnection(ConnectionString);
SqlCommand sCmd = new SqlCommand("_StoredProcedure", sConn);
SCmd.CommandType = CommandType.StoredProcedure;

sCmd.Parameters.Add("@Field1", SqlDbType.NvarChar, 10);
// list of fields by variable

// At this point what's the best/fastest method to transfer the dataset to
the Sql Server table? I could export to XML. There must be an easy method,
but I'm not fluent enough in ADO.NET yet.

Thanks!


Is this a one time exercise, or do you need to do it frequently? If you need to
do it frequently and have access to DTS on the MS SQL Server database, then
create a DTS package to do the transfer of data. Even if it's a one-timer you
can do it very easily with DTS.

If it must be done in an application, then I believe the best way is a row at a
time (but not with a DataSet), even though it may seem tedious, you might be
surprised how fast it is.

Question: In your question you say the file is an "access file", but in your
code you seem to be loading an Excel file. Just curious, not throwing rocks.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
.

Relevant Pages

  • Re: Designing a counter
    ... To do that you could use an update query instead of the ... 'Now append the new record ... lngMaxCount into the SQL correctly. ...
    (microsoft.public.access.formscoding)
  • Re: Identity_Insert wont turn off
    ... I think that the cached connection is the problem. ... We did our upgrades using DTS or the Access upgrade ... I think that you could create your append queries in SQL ...
    (microsoft.public.access.externaldata)
  • Re: append multiple tables
    ... I suggest you first write a sample query that appends a row with fixed ... These must be placed into the SQL genereated in code. ... A single simple append query and no VBA codeing would probably result. ... >> Tom Ellison ...
    (microsoft.public.access.queries)
  • Re: web form getting data to SQL Backend
    ... I have the DB all working correctly so that the Access FE talks to the SQL 08 ... webpage that would dump leads into an Access DB that would append to my BE DB. ... Ultimately I would like there to be a simple web form with about 10 fields ... You don't say which version of SQL-Server you have, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Editing Existing records
    ... one way to do this would be to use code to loop through the dates and append a record or records to the underlying table using SQL ... dim mDate as date _ ... If an activity lasts more than one day, I want the record details to copy to the next date for as long as the activity lasts. ...
    (microsoft.public.access.modulesdaovba)