Don san Don san - 6 months ago 18
SQL Question

Create Table and Migrate Data

I am a newbie to database programming and has a question.

In the C++ application that am working, there's a Oracle DB. One of the table is called USR_INFO. It has multiple columns like,

1. U_INFO_ID
2. U_INFO_FIRST_NAME
3. U_INFO_LAST_NAME
4. U_INFO_ADDRESS
5. U_INFO_AGE
6. U_INFO_LAST_LOGIN_DATE
7. U_INFO_LAST_LOGIN_TIME


Now i want to create a new table called USR_LOG_INFO, and add 3 columns to it called,

1. U_INFO_ID
2. U_INFO_LAST_LOGIN_DATE
3. U_INFO_LAST_LOGIN_TIME


And then migrate the data for same columns from USR_INFO to similar columns in USR_LOG_INFO.

Basically there are 2 steps involved here.

1.Create new table called USR_LOG_INFO

2.Migrate column data from USR_INFO to USR_LOG_INFO

Can somebody give me SQL commands to do above 2 steps?

Answer

easy answer.. don't..

alter table USR_INFO rename USR_LOG_INFO;
alter table USR_LOG_INFO add (U_INFO_ID                NUMBER,
                              U_INFO_LAST_LOGIN_DATE    DATE,
                              U_INFO_LAST_LOGIN_TIME    DATE);

done.

if you really want to do it your way.

create table USR_LOG_INFO as
   select * 
        , to_number(NULL) U_INFO_ID 
        , to_date(null)  U_INFO_LAST_LOGIN_DATE    
        , to_date(null) U_INFO_LAST_LOGIN_TIME
   from USR_INFO;

drop table USR_INFO;

and finally why do you have separate date/time columns you might be better off just using a TIMESTAMP column and extracting the date and/or time when you need it.