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?

• Aaron Bertrand: 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?

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;

• PankajMishra: 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..
• Aaron Bertrand: 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?
• PankajMishra: 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.
• Aaron Bertrand: 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.
• PankajMishra: Now can you please help me with my another Query. dba.stackexchange.com/questions/15451/…