Data from two table in SQL server 2008 R2

Question

i have 2 tables:

Codes table with following column:

                         Name, Code

History table with following column:

                         Name, BU, Plan.

Now, I want code column of Codes table in place of Name column of History table.

I.e History table should contain Code, BU, Plan.

Here how can i achieve this?

Answer 1

Assuming Code is a VARCHAR(32) (just change the first line if my assumption is incorrect):

ALTER TABLE dbo.History ADD Code VARCHAR(32);

UPDATE h
  SET Code = c.Code
  FROM dbo.History AS h
  INNER JOIN dbo.Codes AS c
  ON h.Name = c.Name;

-- verify first, then:

ALTER TABLE dbo.History DROP COLUMN Name;

If you absolutely need the columns to be in the same order (you shouldn't), you can rebuild the table as follows:

-- first, drop all constraints that point to dbo.History. Then:

SELECT Code, BU, Plan 
  INTO dbo.HistoryCopy
  FROM dbo.History;

EXEC sp_rename 'dbo.History', 'dbo.HistoryOld', 'OBJECT';
EXEC sp_rename 'dbo.HistoryCopy', 'dbo.History', 'OBJECT';

-- now re-create the constraints / indexes etc. on the new copy

In order to prevent user activity from affecting or being affected, you'll want to do this during a maintenance window, or surround the above with a serializable transaction.

When you're happy that the change is successful, you can drop the old copy:

DROP TABLE dbo.HistoryOld;