asked on

sql insert records from one database to another database

Hello Experts,
I have 2 databases, each with 1 table.
Database_old , table_old
Database_new, table_new
Each table has name, address, email
In ASP NET, I want to be able to insert all of the records from database_new/table_new into database_old/table_old
Here is the connection string I use for Database_old
H_connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("") & "\app_data\ Database_old.accdb;"

Do I need to open a connection string for Database_new?

What is the sql to insert all records in Database_new/table_new into Database_old/table_old ?

Thanks,
WCGee

Kyle Abrahams, PMP

Yes, you would need to create a connection string for the new db as well.

Basically you need to do it in a few steps.

Open the connection string to the old DB . . . do a select * from old_table.
Execute the command as a datareader.

Build the DataReader (dr) with the following code:

StringBuilder sb = new StringBuilder();
While dr.Read()
{
  sb.AppendLine(String.Format
              ("insert into new_table (name, Address,Email) Values ('{0}', '{1}', '{2}')",
             dr["Name"].ToString(),
             dr["Address"].ToString(),
             dr["Email"].ToString());
}

Open in new window

Then open a connection the new database
Execute a command using stringBuilder.ToString() as NonExecuteQuery
Close your connections and cleanup.

williecg

ASKER

Thanks for your response.

I am a newbee and what I understand is limited.
I conceptually understand your logic, but I get tripped up on the specifics of the commands.
I am using asp net vb.
Could you please provide a code example to do what you described?
Thanks,

WCGee

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

williecg

ASKER

been on holiday. Thanks for the information, getting me in the right direction.