How to update SQL database for the changed data from access

• CDBA

  Hall of Fame

  Points: 3266

  More actions

  September 20, 2006 at 1:56 pm

  #76584

  Hi

   

  Here is the scenario:

   

  1. I have a SQL DB with two tables [Table1 and Table2] 
  2. I get a new access file every week which will contain two tables with only the updated data of table1 and table2 [Which is supposed to be reflected in SQL DB]
  3. I have dependency in two tables. Table 1 has a primary key which is used as foreign key in Table 2 many times for each rows. So while updating, I need to take care of that too.

   

  Can any one kindly suggest?

  
  CDBA

• Jtamil2001

  SSC Enthusiast

  Points: 150

  More actions

  September 20, 2006 at 10:54 pm

  #661626

  hi

  1) create two temp tables in sql db and transfer the access information into the temp tables.

  2) write update statement to your original db comparing the key's of temp table and original table.

  UPDATE table1

  SET col1 = temptable1.col1 FROM  table1 INNER JOIN

                        temptable1 ON table1.col1 = temptable1.col1

  3)now delete rows from temptable1 for those rows available in table1.

  4)after deletion, still if there are any rows in temptable1 then those rows are new rows.

  5) insert new rows in table1.

   

  write down the following sql statment in sqltask and execute the DTS

  For the primary and secondary key combination. Insert the table1 first (primary table) and then the foreign key table

   

  Regards

• CDBA

  Hall of Fame

  Points: 3266

  More actions

  September 22, 2006 at 2:42 pm

  #662042

  Thanks.. It worked

  
  CDBA