Don san Don san - 6 months ago 10
SQL Question

Data migration in Oracle

I am working on a Oracle database. It has a table called USR_INFO and has following columns.


  1. U_ID

  2. U_NAME

  3. U_ROLE



The U_ROLE can be a value of "A", "B" or "C".

Now i want to create a new table called ROLE_INFO having following columns


  1. ROLE_ID

  2. ROLE_NAME

  3. ROLE_TAG



The task is to query in USR_INFO and look for U_ROLE == "B", and then if it matches
then copy U_ID to ROLE_ID and U_NAME to ROLE_NAME. I want to do that for all rows
in USR_INFO. While doing that, i also want to insert "BB" to ROLE_TAG.

Once am done with copying all data to ROLE_INFO. I want to search for U_ROLE == "B"
in USR_INFO and delete all those rows without affecting others.

This is what i did so far.

Create new Table called ROLE_INFO

CREATE TABLE ROLE_INFO
(
ROLE_ID INT,
ROLE_NAME VARCHAR(255),
ROLE_TAG VARCHAR(10)
);


Now for copying, i did this,

INSERT INTO ROLE_INFO (ROLE_ID, ROLE_NAME)

SELECT U_ID, U_NAME

FROM USR_INFO

WHERE U_ROLE == "B"


// When i do the insert, how to make sure that ROLE_TAG gets populated with "BB"?

For deleting

DELETE FROM USR_INFO

WHERE U_ROLE == "B";


// Will this just delete the row that's already been copied to ROLE_INFO? Or will it delete more?

Answer

You can modify the insert this way to add "BB" to your insert statement (note the single = instead of ==) . Also replace "B" with 'B' in your where clause.

INSERT INTO ROLE_INFO (ROLE_ID, ROLE_NAME , ROLE_TAG)
SELECT U_ID, U_NAME,'BB'
FROM USR_INFO
WHERE U_ROLE='B';

COMMIT;

DELETE FROM USR_INFO
WHERE U_ROLE='B';

COMMIT;

Since your select statement and the delete statement is checking for the same condition the delete statement will only remove the rows where U_ROLE="B" (please note you need to use single equals as well).

Also once you perform the insert statement, you need to issue a commit statement so that the changes are persisted and visible.