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?

  • Does either table have a primary key? What is it? What does SELECT KPI, COUNT(*) FROM dbo.Codes GROUP BY KPI return? How about SELECT KPI, COUNT(*) FROM dbo.History GROUP BY KPI? – Aaron Bertrand Mar 23 '12 at 9:42

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

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

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

-- verify first, then:


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;
  • Yepp, Code is NVARCHAR(Max), which is just a short code for Name column of History Table. When i tried this code it was giving me 3 times the original row. Lets say i should have 30 rows but after execution of this it was giving me 90 rows as answer.. – Pankaj Mar 23 '12 at 9:34
  • Then it sounds like Name is not unique. Please describe the schema of the tables including keys and maybe some sample data / how you want it to look when the change is complete. Otherwise we're guessing. Also, why are you using NVARCHAR(MAX) for a "short" code? – Aaron Bertrand Mar 23 '12 at 9:36
  • Yeah name is not unique, I have attached the image may b it will help u a abit to understand my problem. Schema is dbo, And i havnt specified any key. Sorry i am new on SQL server so may be i will not be able to answer properly. Apologies. – Pankaj Mar 23 '12 at 9:43
  • Without seeing the duplicates I don't understand how you ended up with 90 rows instead of 30. If you don't absolutely need the column order to be the same, then just execute the first portion. – Aaron Bertrand Mar 23 '12 at 9:45
  • Now can you please help me with my another Query. dba.stackexchange.com/questions/15451/… – Pankaj Mar 23 '12 at 9:54

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.