locked
How to add in 2 columns of a new table in a new database to existing table? RRS feed

  • Question

  • User2142845853 posted

    <p>I have an MVC C# web (classic not core) and sql database.&nbsp; The goal is to merge a 2nd database table and take 2 columns . If sql it might be

    SELECT * FROM TABLE1 INNER JOIN
    SELECT NAU.ROLES, NAU.ID
    FROM NEWDB.ASPNETUSER NAU
    WHERE NAU.ID = TABLE1.ID
    SORT BY TABLE1.LASTNAME

    as long as 2 new colums are there to pick from based on id



    </p>
    Saturday, April 20, 2019 7:11 PM

Answers

  • User-893317190 posted

    Hi rogersbr ,

    It seems that you want to update one table's two column with   data in another two columns in another table which is in another database.

    If so , you could try the code below.

    Say I have table named first in database entityexe.

    id	Info	amount	catname	desc
    1	Basic	10.23	NULL      NULL
    2	Day All	123.23	NULL      NULL
    3	HRA	15.78	NULL      NULL
    4	PA	126.13  NULL      NULL	
    5	ESI	100.00	NULL      NULL
    1002	visual	45.60	NULL	  NULL

    Then I have another table named  dbo.Categories  in database Northwind.

    CategoryID	CategoryName	Description
    1	       Beverages	Soft drinks, coffees, teas, beers, and ales
    2	       Condiments	Sweet and savory sauces, relishes, spreads, and seasonings
    3	       Confections	Desserts, candies, and sweet breads
    4	       Dairy Products	Cheeses
    5	       Grains/Cereals	Breads, crackers, pasta, and cereal
    6	       Meat/Poultry	Prepared meats
    7	         Produce	Dried fruit and bean curd
    8	          Seafood	Seaweed and fish

    The I could update first 's catname and desc column with Categories's CategoryName  and Description column in the sql below.

    You could use syntax Northwind.dbo.Categories to refer to table in another database , if your another database is in the same server.

     update   [first]  set catname = (select categoryname from Northwind.dbo.Categories where CategoryID=first.id) 
       , [desc] =  (select [Description] from Northwind.dbo.Categories where CategoryID=first.id)

    The result.

    id	Info	amount	catname	          desc
    1	Basic	10.23	Beverages	Soft drinks, coffees, teas, beers, and ales
    2	Day All	123.23	Condiments	Sweet and savory sauces, relishes, spreads, and seasonings
    3	HRA	15.78	Confections	Desserts, candies, and sweet breads
    4	PA	126.13	Dairy Products	Cheeses
    5	ESI	100.00	Grains/Cereals	Breads, crackers, pasta, and cereal
    1002	visual	45.60	NULL	         NULL

    You could also refer to the link below to learn how to import data  in another database's table.

    https://stackoverflow.com/questions/12558783/move-a-table-from-one-database-to-another-database-sql-server

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 22, 2019 2:20 AM