sql-serversql-server-2014

Adding columns from a different SQL Server database


I'm trying to make a temporary table to put unrelated columns into to use later. I can create the table, and it will create the columns that I alter the table to have, however it will not update those columns with the data.

SELECT * 
INTO testDB.dbo.t 
FROM Categories;

ALTER TABLE testDB.dbo.t 
ADD RegionID INT, RegionDescription varchar(255);

UPDATE testDB.dbo.t 
SET RegionID = Region.RegionID, 
    RegionDescription = Region.RegionDescription
FROM Region 
INNER JOIN t ON Region.RegionID = t.RegionID;

When I run this I get "error Object t not valid". Categories imports fine. I'm just trying to add those two columns from Region.

CategorID CategoryName Description Picture RegionID RegionDescr
1 Beverages Soft drinks, teas <BINARY DATA> NULL Null

I want:

CategorID CategoryName Description Picture RegionID RegionDescr
1 Beverages Soft drinks, teas <BINARY DATA> data data

data is from another table in another database. There are not as many rows in the other database as categoryID's. Only categoryID is set to NOT NULL.


Solution

  • UPDATE t
    SET RegionID = Region.RegionID, 
        RegionDescription = Region.RegionDescription
    FROM Region 
    INNER JOIN testDB.dbo.t t ON Region.RegionID = t.RegionID;