0

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?

1
  • 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? Commented Mar 23, 2012 at 9:42

1 Answer 1

3

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;
6
  • 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
    Commented Mar 23, 2012 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? Commented Mar 23, 2012 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
    Commented Mar 23, 2012 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. Commented Mar 23, 2012 at 9:45
  • Now can you please help me with my another Query. dba.stackexchange.com/questions/15451/…
    – Pankaj
    Commented Mar 23, 2012 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.