CODEWITHSUNDEEP
sqlsql-servert-sql
Miguel Moura
Miguel Moura

Reputation: 39364

Change values when copying records from one table to another

I have the following code to copy data from one table to another:

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT 
  CategoryId, Author, Title
FROM MyDbBackup.Books

I need to apply the following transformation when copying CategoryId values:

+---------------------+-----------------+
|     Old CategoryId  |  New CategoryId |
+---------------------+-----------------+
|                  1  |               2 |
|                  2  |               1 |
|                  3  |               3 |
|                  4  |               4 |
|                  5  |               8 |
|                 14  |               6 |
|                 15  |               7 |
|                 18  |               5 |
|                 22  |               9 |
+---------------------+-----------------+

How can I do this?

Upvotes: 2

Views: 75

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use case when 

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT 
  case when CategoryId = 1 then 2
       when CategoryId = 2 then 1
       when CategoryId = 5 then 8
       when CategoryId = 14  then 6
       when CategoryId = 15  then 7
       when CategoryId = 18  then 5
       when CategoryId = 22  then 9
       else cateogoryId end, Author, Title
FROM MyDbBackup.Books

or a less verbose way 

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT 
  case CategoryId 
       when  1 then 2
       when 2 then 1
       when 5 then 8
       when 14  then 6
       when 15  then 7
       when 18  then 5
       when 22  then 9
       else cateogoryId end, Author, Title
FROM MyDbBackup.Books

Upvotes: 1

Racil Hilan
Racil Hilan

Reputation: 25341

Since there is no formula, then the conversion must be done one by one:

INSERT INTO MyDb.Books (CategoryId, Author, Title)
SELECT CASE CategoryID WHEN 1 THEN 2
                       WHEN 2 THEN 1
                       WHEN 5 THEN 8
                       WHEN 14 THEN 6
                       WHEN 15 THEN 7
                       WHEN 18 THEN 5
                       WHEN 22 THEN 9
                      ELSE CategoryId ID END AS CategoryId
       , Author, Title
FROM MyDbBackup.Books

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method is to use a lookup table a join:

select v.newid, b.author, b.title
from MyDbBackup.Books b join
     (values (1, 2), (2, 1), (3, 3), (4, 4), (5, 8), (14, 6), (15, 7), (18, 5), (22, 9)
     ) v(oldid, newid)
     on b.CategoryId = v.oldid;

An alternative is to use a case expression. However, using the join ensures that only the set of books with the old ids is in the result set. So, it does both the lookup and filtering.

If you don't want the filtering, you can use a left join instead of an inner join.

Upvotes: 1

Thom A
Thom A

Reputation: 95554

As said in the comments by Panagiotis Kanavos, you'll need to use a CASE expression:

CASE CategoryID WHEN 1 THEN 2
                WHEN 2 THEN 1
                WHEN 5 THEN 8
                WHEN 14 THEN 6
                WHEN 15 THEN 7
                WHEN 18 THEN 5
                WHEN 22 THEN 9
                ELSE CategoryID END

Upvotes: 1

Related Questions