| Looking for a simple way to clone data (intra table). [message #372038] |
Fri, 05 January 2001 09:35  |
Christopher Beattie
Messages: 5
Registered: January 2001
|
Junior Member |
|
|
I have what at face value appears to be a simple problem. I have several tables that contain data that is dependant upon a date value. In the field, procedures will update this table on a daily basis, but for testing purposes, we need a way to copy all the data for a given date into a second date.
The code I ingerited uses cursors. A sample (simplified greatly) follows:
PROCEDURE COPY_RECORDS_BY_DATE
( ofDate IN Date, toDate IN Date )
IS
CURSOR myCur IS
SELECT * from THE_TABLE
WHERE DATE = ofDate;
BEGIN
FOR myRec IN myCur
LOOP
myRec.DATE := toDate;
INSERT INTO PORTFOLIO_SNAPSHOT (
DATE ,
FIELD_2 ,
FIELD_3 ,
FIELD_ETC)
VALUES (
myRec.DATE ,
myRec.FIELD_2 ,
myRec.FIELD_3 ,
myRec.FIELD_ETC);
END LOOP;
END;
This works, but thetables involved have a large number of fields, not just the few I listed in my sample. Each procedure spans several pages and it's not always easy to chec t make sure that every field gets mapped back to its own proper filed. More importantly, since this is used for testing purposes, it requires constant updating should the schema change (and that's almost guarenteed when testing anything this large).
What annoys me is that the record fetched from the cursor aready contains all the information about the table that I need. Is there an easier way to allow me to insert modified recors from a cursor without having to specify the table structure in the process?
Thanks in advance,
|
|
|
|
| Re: Looking for a simple way to clone data (intra table). [message #372064 is a reply to message #372038] |
Mon, 08 January 2001 14:48  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
would something like this help?
PROCEDURE COPY_RECORDS_BY_DATE
( ofDate IN Date, toDate IN Date )
IS
v_sql_stmt varchar2(100);
begin
v_sql_stmt := 'truncate table PORTFOLIO_SNAPSHOT';
EXECUTE IMMEDIATE (v_sql_stmt);
v_sql_stmt := ' insert into PORTFOLIO_SNAPSHOT (select * from THE_TABLE where DATE = ofDate';
-- maybe it's :ofDate in the above statement!
EXECUTE IMMEDIATE (v_sql_stmt);
v_sql_stmt := update PORTFOLIO_SNAPSHOT
set DATE = toDate';
-- or is it :toDate ?
EXECUTE IMMEDIATE (v_sql_stmt);
END;
Take a look at Tempory tables in Oracle 8, they are destroyed when you end your session. Your example cant support MULTIPLE users if that is important.
|
|
|
|
| Re: Looking for a simple way to clone data (intra table). [message #372083 is a reply to message #372038] |
Tue, 09 January 2001 08:57 |
Christopher Beattie
Messages: 5
Registered: January 2001
|
Junior Member |
|
|
Thanks of the comment. Unfortunately, In simplifying my code for the question, I forgot to change a name, and this may have lead to some confusion. I had intended to convert all references to PORTFOLIO_SNAPSHOT to THE_TABLE, in other words from the select, once the date is modified it is to be inserted into the same table, THE TABLE. Thus insert with select and later modification can't work because it duplicates the records in the insert statement (Or worse) thus making the update work on both the old and new records.
I will look into the temp table option. In this case these are admin functions, and multiple users should not be accessing this function at the same time.
Christopher Beattie
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
