Migration Script using a query [message #392288] |
Tue, 17 March 2009 05:44  |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi,
i am creating a table like
CREATE TABLE EMRLoginConfigInfo(
GROUP_ID NUMBER(20,0) ,
MAX_PASSWORD_LENGTH NUMBER(1,0) Default 8,
MIN_CHAR NUMBER(1,0) default 4,
MIN_ALPHANUM_CHAR NUMBER(1,0) default 2,
LOGIN_ATTEMPT NUMBER(1,0) default 3,
LOGIN_LOCKING_PERIOD NUMBER(2,0) default 30,
LAST_PW_NOTREUSE NUMBER(2,0) DEFAULT 5,
PASSWORD_EXPIRY NUMBER(2,0) DEFAULT 30,
CREATED_DATE DATE NOT NULL,
MODIFIED_DATE DATE NOT NULL
)
/
and i am having a second table like Emrgroupmaster which is having some values in it.
select * from Emrgroupmaster;
group_id group_name status logo_id
1501 CCHITTES 1
1518 NiClinic 1
1505 00000amj 1
1506 Columbia 1
1509 prvshash 1
1502 00pvtlic 1
1503 Aksh_New 1
1504 0PvtOrcl 1
1512 PrivatLi 1
1514 00aaaaaa 1
1508 privalli 1
1507 000000NP 1
1513 gp_pri_k 1
1515 00000pvt 1
1516 peacocks 1
1517 000pecos 1
1511 00AshPvt 1
1510 00emrpvt 1
Now i have to insert the values into the above table dynamically using a single query.how is it possible?
|
|
|
|
|
|
|
|
Re: Migration Script using a query [message #392299 is a reply to message #392296] |
Tue, 17 March 2009 06:16   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
No. That is not the correct way.
SQL> INSERT INTO TEST_TAB(col_1, col_2, col_3)
2 SELECT 'A' FROM Dual;
INSERT INTO TEST_TAB(col_1, col_2, col_3)
*
ERROR at line 1:
ORA-00947: not enough values
SQL> INSERT INTO TEST_TAB(col_1)
2 SELECT 'A' FROM Dual;
1 row created.
Only you know what columns from the source tables are used to populate the target columns. The number of columns in the INSERT Statement should be equal to the number of columns you specify in the SELECT Statement. The columns you skip in the insert statements will be populated with NULL values unless you have a default value set on it. I suggest you read up on INSERT Statement in Oracle.
Hope this helps.,
[Added]
Didn't see your last post. So what are the use of the two date columns CREATED_DATE and MODIFIED_DATE? How you plan to populate them?
Regards,
Jo
[Updated on: Tue, 17 March 2009 06:22] Report message to a moderator
|
|
|
|
|
Re: Migration Script using a query [message #392302 is a reply to message #392300] |
Tue, 17 March 2009 06:23   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
based on the group_id i will insert rows into the first table
like
insert into EMRLOGINCONFIGINFO(group_id) select group_id from Emrgroupmaster
then what about rest of columns they will be inserted with default values?
|
|
|
|
|
|
|
Re: Migration Script using a query [message #392310 is a reply to message #392299] |
Tue, 17 March 2009 06:33   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@rajasekhar857,
Hope you didn't see this msg added in my previous post:
joicejohn wrote on Tue, 17 March 2009 16:46 |
[Added]
Didn't see your last post. So what are the use of the two date columns CREATED_DATE and MODIFIED_DATE? How you plan to populate them?
|
You have set these columns as NOT NULL with no default value. So you have to populate data in these columns also everytime you INSERT a record.
insert into EMRLOGINCONFIGINFO(group_id, CREATED_DATE, MODIFIED_DATE)
Select group_id, sysdate, sysdate from Emrgroupmaster
That is just an example. You should follow your business requirement/rules as to what date goes into those columns.
Hope its clear to you.
[Added]
Oops!!! didn's see Michel's and OP's post.
Regards,
Jo
[Updated on: Tue, 17 March 2009 06:34] Report message to a moderator
|
|
|
|
|