locked
How to copy data from DB1.table to DB2.table RRS feed

  • Question

  • User2010540170 posted

    I have two Databases, DB1 and DB2.
    In each DB i have a table called dbo.vendor.
    In the vendor table i have 10 columns;
    in DB1 ONLY the "vendorName" column is populated with 300 total items.
    in the vendor table of DB2 I have 750 total items, which include the 300 from DB1, as well as 450 unwanted items.
    How can I copy over all columns, including the 9 that are blank in DB1, from DB2 ONLY where the vendorNames are the
    same

    EXAMPLE:
    DB1 -
    dbo.vendor
    'vendorName', 'address', 'email', 'CCtype', 'CCPreference',
    1. 'abc', NULL NULL NULL NULL
    2. 'bfg', NULL NULL NULL NULL
    3. 'popo', NULL NULL NULL NULL
    ETC...


    DB2 -
    dbo.vendor
    'vendorName', 'address', 'email', 'CCtype', 'CCPreference',
    1. 'abc', '123 abd st', 'a@email.com', 'AMEX', 'Email'
    2. 'bfg', '555 tully ave', 'tr@gmail.com', 'VISA', 'Letter'
    3. 'jeff', '765 asd st', 'j@pop.com', 'AMEX', 'Letter'
    4. 'popo', 9087 trace rd', 'ma@yahoo.com', 'AMEX', 'EMAIL'

    I would like to pull over ALL COLUMNS where DB1.dbo.vendor.vendorname = DB2.dbo.vendor.vendorname

    Tuesday, June 25, 2013 3:51 PM

Answers

  • User-431249759 posted

    INSERT INTO DB1.vendor (vendorName, address, email, CCtype, CCPreference,)
    SELECT vendorName, address, email, CCtype, CCPreference,
    FROM DB2.vendor 
    WHERE ....

    right click on your table from the SQL Management Studio, select Tasks, export and there use the wizard to create an export query. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 25, 2013 3:59 PM

All replies

  • User-431249759 posted

    INSERT INTO DB1.vendor (vendorName, address, email, CCtype, CCPreference,)
    SELECT vendorName, address, email, CCtype, CCPreference,
    FROM DB2.vendor 
    WHERE ....

    right click on your table from the SQL Management Studio, select Tasks, export and there use the wizard to create an export query. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 25, 2013 3:59 PM
  • User-1716253493 posted

    use alias

    db1.tablename as t1 and db2.tablename2 as t2

    Tuesday, June 25, 2013 11:23 PM