CODEWITHSUNDEEP
c#databasems-accessduplicatesinsert-into
faheem khan
faheem khan

Reputation: 481

Insert from One MS Access database to Another MS Access database

I have to take the data from MS Access DB to another. This was insert statement used 

 cmd.CommandText = @"Insert Into [MS Access;PWD=pw;DATABASE=" + currentDBPath + @"].[Orders]  Select   *  FROM  [Orders]  where OrderDate>=#" + from.ToShortDateString() + "# and  OrderDate <=#" + to.ToShortDateString() + "# and  IsCustomer=Yes  ";

This statement works fine. But some one started to enter the data directly to target database and also in the source. This created duplicated records. Now I want to copy those orders from source which are not in the target DB's table.

Upvotes: 1

Views: 322

Answers (2)

faheem khan
faheem khan

Reputation: 481

I didn't find any solution to what I was looking for in my question , So I decided to just delete the duplicated data in destination database.

I used the below statement to delete the data ,

   Delete * from Orders where AutoOrderID   in (SELECT Orders.AutoOrderID FROM Orders Inner JOIN OrdersSource ON OrdersSource .OrderId = Orders.OrderId and OrdersSource .OrderDate=Orders.OrderDate);

Upvotes: 1

marlan
marlan

Reputation: 1485

Assuming Orders table has a primary key named IDOrers, you must bring both tables to one DB, so you can compare data.
Easy option: have in Sours DB a link to [Orders] on Destination DB, named OrdersDest. In that case you create on destination a query of missing orders, named MissingOrders : 

SELECT Orders.* FROM Orders LEFT JOIN OrdersDest ON OrdersDest.IDOrders = Orders.IDOrders WHERE OrdersDest.IDOrders Is Null

Your Command will now look like this: 

cmd.CommandText = @"Insert Into [MS Access;PWD=pw;DATABASE=" + currentDBPath + @"].[Orders]  Select   *  FROM  [MissingOrders]  where OrderDate>=#" + from.ToShortDateString() + "# and  OrderDate <=#" + to.ToShortDateString() + "# and  IsCustomer=Yes ";

You could also pass the data through this linked table:

cmd.CommandText = @"Insert Into [OrdersDest]  Select * FROM [MissingOrders]  where OrderDate>=#" + from.ToShortDateString() + "# and  OrderDate <=#" + to.ToShortDateString() + "# and  IsCustomer=Yes  ";

Upvotes: 1

Related Questions