Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
    
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
    
Yeah it helped me. Thanks. –  Pankaj Mar 23 '12 at 9:51
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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