ID Address Location Country
1   NULL     Region1  Austrailia
2   NULL     Region2  Antartica
3   NULL    Region3   Egypt

ID  Address        Name
1   test avenue    james
2   test avenue 2  jessica
3   test avenue 3  joshua

I am working on a query to migrate data Address data from Database 1, TableB to Database 2 TableA. So far I have tried to do the query below however that only inserts one record for the entire database, I want to insert each new sequential address into TableA as it appears in TableB.

UPDATE [dbo].[TableA]
SET [dbo].[TableA].[Address]= Table2.ID
        FROM [TableB]) AS Table2
WHERE [dbo].[TableA].[ID] <= 1449

This results in all records only having the first ID which is 1, so all the rows in TableA are Address = 1, instead of 1,2,3 etc. I think I am close but not quite sure what I am missing

Desired Result

    ID Address Location Country
    1   1        Region1  Austrailia
    2   2        Region2  Antartica
    3   3        Region3   Egypt
  • Do you mean, for each ID on table A add all addresses of table B? Or TableA.ID = TableB.ID? – McNets Feb 6 '20 at 21:39
  • @McNets For each ID of TableB, add TableB.ID into TableA.Address – kabuto178 Feb 6 '20 at 21:42
  • Please, could you add the desired result of your sample data? – McNets Feb 6 '20 at 21:43
  • @McNets edited question with desired result, so populate TableA with the ID's of TableB – kabuto178 Feb 6 '20 at 21:51
  • Are you sure updating ID is a good idea? – Eric Feb 6 '20 at 22:28

You can try this

SET a.Address = b.ID
FROM TableA a
JOIN TableB b ON b.ID = a.ID
WHERE a.ID <= 1449
  • I tried this, sadly it has no effect on the data in the column I want to update. – kabuto178 Feb 7 '20 at 2:13
  • Not sure if the JOIN is what is causing the problem being that both tables have nothing really to join on, perhaps – kabuto178 Feb 7 '20 at 2:14
  • If there's nothing to join on, how do you know which row is assigned to which row on the other table? – Eric Feb 7 '20 at 17:03
  • Based on your sample data, my code will work fine. – Eric Feb 7 '20 at 17:05
  • Well the ID's that I want to copy over are sequential, so for example 1,2,3,4,5.... etc. I just want to use the numbers that exists in TableB to populate the Address column in tableA – kabuto178 Feb 7 '20 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.