Home » SQL & PL/SQL » SQL & PL/SQL » Migration Script using a query (Oracle 10g)
Migration Script using a query [message #392288] Tue, 17 March 2009 05:44 Go to next message
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 #392290 is a reply to message #392288] Tue, 17 March 2009 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert ... select ...

Regards
Michel
Re: Migration Script using a query [message #392292 is a reply to message #392290] Tue, 17 March 2009 05:49 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Micheal can u give me the complete query as each record should be inserted into EMRLoginConfigInfo.
Re: Migration Script using a query [message #392294 is a reply to message #392292] Tue, 17 March 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only YOU know what should be in each target column from source ones.

Regards
Michel
Re: Migration Script using a query [message #392296 is a reply to message #392292] Tue, 17 March 2009 05:59 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
INSERT INTO EMRLOGINCONFIGINFO
(GROUP_ID,MAX_PASSWORD_LENGTH,MIN_CHAR,MIN_ALPHANUM_CHAR,
LOGIN_ATTEMPT,LOGIN_LOCKING_PERIOD,LAST_PW_NOTREUSE,
PASSWORD_EXPIRY) 
SELECT GROUP_ID FROM EMRGROUPMASTER
/

IS THIS CORRECT WAT TO INSERT THE VALUES?

[Updated on: Tue, 17 March 2009 06:15] by Moderator

Report message to a moderator

Re: Migration Script using a query [message #392298 is a reply to message #392296] Tue, 17 March 2009 06:07 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
there is only one matched column from both tables also the last two columns are not null values.how to frame such query in this scenario,i am actually new in this type of context.can anyone help me out?
Re: Migration Script using a query [message #392299 is a reply to message #392296] Tue, 17 March 2009 06:16 Go to previous messageGo to next message
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 #392300 is a reply to message #392296] Tue, 17 March 2009 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep code tags for code or output.
Keep your lines in 80 characters.
Use SQL Formatter to improve statement readability.
Don't write the whole post in UPPER case.

You must have the same number and matching columns in INSERT and in SELECT parts.

Regards
Michel

Re: Migration Script using a query [message #392301 is a reply to message #392298] Tue, 17 March 2009 06:19 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
rajasekhar857 wrote on Tue, 17 March 2009 06:07
there is only one matched column from both tables also the last two columns are not null values.how to frame such query in this scenario,i am actually new in this type of context.can anyone help me out?


what exactly you want?

Michel clearly said that
Quote:
Only YOU know what should be in each target column from source ones.

Regards
Michel



so fast? i am having some network problem.
Re: Migration Script using a query [message #392302 is a reply to message #392300] Tue, 17 March 2009 06:23 Go to previous messageGo to next message
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 #392305 is a reply to message #392302] Tue, 17 March 2009 06:28 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i need all the columns to be filled in the first table but group_id should be inserted same from the second table?
Re: Migration Script using a query [message #392306 is a reply to message #392302] Tue, 17 March 2009 06:29 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
No..... as created_date,modifies_date columns have no default values specified.rest of the columns inserted with defaults.
Re: Migration Script using a query [message #392308 is a reply to message #392305] Tue, 17 March 2009 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All columns but date ones have default value so no problem.
For date columns you can put sysdate value.

Regards
Michel
Re: Migration Script using a query [message #392309 is a reply to message #392306] Tue, 17 March 2009 06:31 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yes in the same way can you give me the query please it is very
hard as i am a beginner?help me out
Re: Migration Script using a query [message #392310 is a reply to message #392299] Tue, 17 March 2009 06:33 Go to previous messageGo to next message
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

Re: Migration Script using a query [message #392311 is a reply to message #392309] Tue, 17 March 2009 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read and study the link provided by joicejohn?
There are many examples, read them, you should be able to then write your query.

Regards
Michel
Re: Migration Script using a query [message #392318 is a reply to message #392311] Tue, 17 March 2009 07:00 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Thank you very much for all your valuable suggestions
Previous Topic: DBMS_JOB
Next Topic: decode (to_date to_char., default) (merged)
Goto Forum:
  


Current Time: Mon Dec 05 03:06:12 CST 2016

Total time taken to generate the page: 0.06597 seconds