SQL server 2008 , add a column about the description of another column in a Table

Question

I need to insert one column (varchar) from one table to another table on SQL server 2008.

Table 1:

Col1  col2  col3 
Bdh    6       28435
Bdh    2       69
dwd    0       57
dwd    8       9742

I need to add a column about the description of col1 in Table 1.

e.g .

Col1    id_desc          col2   col3 
Bdh     good               6       28435
Bdh     good               2       69
dwd     excellent          0       57
dwd     excellent          8       9742

Table 2:

      Col1  id_desc 
      Bdh     good
      dwd     excellent

SQL:

  INSERT into  Table 1
  SELECT  b.id_desc as id_desc
  FROM Table2 as b
   where a.Col1 = b.Col1

It does not work.

The table has many records so it is not efficient to do it manually.

Any help would be appreciated.

what is a.code ? and on what basis do you want to set Id_Desc as good or excellent? — Karthik Ganesan, Jun 03 '14 at 19:12
You will need to use an update statement. Is ur intention to set all the columns as good.. or just the ones that have code = 5 ? — TMNT2014, Jun 03 '14 at 19:15
Given that `id_desc` will apparently be dependent on `Col1`, have you considered creating a view rather than adding a column to `Table 1`? — Joe Farrell, Jun 03 '14 at 19:22

score 0 · Answer 1 · edited May 23 '17 at 12:05

You can't insert id_desc into table 1 without first altering table 1 to include a column which has the same datatype as id_desc.

It would be easier to use select...into to create a new table from a table expression that joins table 1 and table 2:

select
    a.col1
    ,b.id_desc
    ,a.col2
    ,a.col3
into newTable
from table1 as a
inner join table2 as b
    on a.col1 = b.col1

Alternately, if you want to add a new column to table 1 so you can insert into it or update it, read this.

Then you would either perform the insert or the update.

score 0 · Accepted Answer · answered Jun 03 '14 at 19:35

Here's a short script that shows how to add a new column to the existing table and fill in the relevant data:

-- Here I'm just setting up the tables with your example data.
create table [Table 1] (Col1 varchar(16), Col2 int, Col3 int);
create table [Table 2] (Col1 varchar(16), id_desc varchar(16));
go

insert into [Table 1] values 
    ('Bdh', 6, 28435),
    ('Bdh', 2, 69),
    ('dwd', 0, 57),
    ('dwd', 8, 9742);
insert into [Table 2] values
    ('Bdh', 'good'),
    ('dwd', 'excellent');
go

-- First, add the column. It must either be nullable or have a default value,
-- since there are extant records.
alter table [Table 1] add id_desc varchar(16) null;
go

-- Now merge the data from [Table 2] into the new column.
update [Table 1]
set id_desc = T2.id_desc
from
    [Table 1] T1
    inner join [Table 2] T2 on T1.Col1 = T2.Col1;

Afterward, you can alter the column to be non-nullable if you wish; I've just made it null initially because SQL Server needs to have some default value that it can insert into the new column for existing rows.

There are plenty of ways to do the last part if you don't like the UPDATE...FROM syntax; for instance, you can use MERGE or a CTE with an UPDATE.

Finally, I mentioned this in a comment above, but you might also consider creating a view:

create view Table1VW as
select
    T1.Col1,
    T2.id_desc,
    T1.Col2,
    T1.Col3
from
    [Table 1] T1
    inner join [Table 2] T2 on T1.Col1 = T2.Col1;

You may have some reason why you'd rather modify your table instead, but in the absence of such a reason, this might be a good alternative as it avoids redundant data storage and possible consistency issues later.

SQL server 2008 , add a column about the description of another column in a Table

2 Answers2