CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
j_t_fusion
j_t_fusion

Reputation: 223

SQL: replace original column with new column which references to another table's column

I have two tables, both already populated with data, and with the following columns:

Table1
jobType char
jobDesc varchar

New_table
jobID int
jobType char

I want to replace Table1.jobType with a jobID column, such that Table1 will now reference to New_table using jobID. Any existing data in Table1.jobType must be "translated" to the appropriate jobID.

Upvotes: 0

Views: 920

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74177

The basic drill:

  • Start with the original job table

    create table dbo.job
(
  id              int          not null identity(1,1) ,
  job_type        varchar(64)  not null ,
  job_description varchar(128) not null ,

  constraint job_PK primary key clustered (id) ,

)

  • Create the new job type table (don't forget to populate it)

    create table dbo.job_type
(
  id   int         not null identity(1,1) ,
  name varchar(64) not null ,

  constraint job_type_PK   primary key clustered ( id   ) ,
  constraint job_type_AK01 unique nonclustered   ( name ) ,

)

  • Alter the table dbo.job to add a new job_type_id column

    alter table dbo.job
add job_type_id int null

  • Update the job table, mapping the value of job_type_id based on the [old] job_type column's value.

    update dbo.job
set job_type_id = jt.id
from dbo.job      j
join dbo.job_type jt on jt.name = j.job_type

  • Alter the new column to make it non-nullable. This will fail until you've ensured that all rows have a non-null value for job_type_id:

    alter table dbo.job
alter column job_type_id int not null

  • Add the new foreign key constraint needed

    alter table dbo.job
add constraint job_AK01
foreign key ( job_type_id )
references dbo.job_type ( id )

  • The last and irrevocable step is to drop the old column. This will break any queries, stored procedures, etc. that reference this column...but you already did your homework in this department and have coordinated all necessary changes, right?

    alter table dbo.job drop column job_type

Upvotes: 1

Pete Carter
Pete Carter

Reputation: 2731

If the data in the JobType column in Table1 are all Integer values stored as text, then run the following:

ALTER TABLE Table1 ALTER COLUMN JobType INT

If the column contains non integer values, such as ABC1, ABC2 then you will first need to alter the data in the column. With the example value I used here, you would run:

UPDATE Table1
SET JobType = SUBSTRING(JobType,4,1)

Then you can run the ALTER TABLE statement above

Upvotes: 0

Related Questions