convert flat-file data to relational

convert flat-file data to relational

Post by Kevin Meffor » Thu, 22 Jan 1998 04:00:00



First, my apologies for the length of this, but it's a little hard to
describe my situation.

I'm trying to convert data from a wide, flat-file layout to a relational
model.  Currently, I have a
'tmp_member' table:

global_id
variable1
variable2
variable3
...
variablen

and I need to convert it to:

global_id
q_name
q_value

where q_name is the name of the tmp_member column and q_value is the
value stored in tmp_member.variableN.

I've written a stored procedure (see below) to read through a questnames
table which contains the column names (in tmp_member) and the desired
q_names in the new table.  The problem is, because there are thousands
of insert statements, I fill up my transaction log and the procedure
can't finish.  

Is there a better,faster,easier way to accomplish this?  Any help would
be appreciated.

My test data has around 50,000 records in tmp_member and 165 variables.
My final dataset will contain several hundred thousand records (and same
number of vars), so I know if this is a space problem now, it will be
impossible then.

/* update 'tmp_question' table */
/* cursor to scan through questnames table for question names and values
*/
declare questnames_curs cursor
for
select tmp_name,real_name,multi from mrd..questnames
for read only

/* open cursor for processing */
open questnames_curs

/* variables to hold cursor values for queries */
declare






/* walk through the 'questnames' table and insert questions  */
/* into 'tmp_question' table after proper formatting         */


begin
  /* single response questions */

    execute("insert into tmp_question
(global_id,q_name,q_value,version,source) select global_id,



tmp_question.global_id = tmp_member.global_id)")

    begin




        begin

(global_id,q_name,q_value,version,source) select global_id,

q_value,form_num,pub_code from tmp_member where

not exists(select global_id from tmp_question where tmp_question.q_name


tmp_member.global_id)"


        end
    end
  /* get the next record in questnames */

end

/* close and deallocate the cursor now that everything's done */
close questnames_curs
deallocate questnames_curs

 
 
 

convert flat-file data to relational

Post by John Barsto » Fri, 23 Jan 1998 04:00:00



>I've written a stored procedure (see below) to read through a questnames
>table which contains the column names (in tmp_member) and the desired
>q_names in the new table.  The problem is, because there are thousands
>of insert statements, I fill up my transaction log and the procedure
>can't finish.

You could put a DUMP TRANS statement just outside the WHILE loop in your
stored procedure.
Alternatively, if you have a lot of disk space, you could put the
transaction log on a really big device.
--
John C. Barstow
Microcrafts, Inc.
http://www.microcrafts.com
425-250-0000

 
 
 

1. Help converting Flat file DB to Relational

Hi,

I am new to this list.

I am desparate to find info about relational databases and using scanners
for input into relational databases.....

I am currently using an outdated Flat file database program (Q&A for
Windows) that is no longer being upgraded or supported by the manufacturer
(latest version is for Windows 3.1) and it has become quite cumbersome in
the amount of data it contains (since it is flat file and all)......

Additionally the data entry into the flat file DB is killing me and so I
need to update to relational database with the ability to scan data into
database..... but I have absoultely no experience in relational
databases.....

I have some BASIC programming skills and I completely designed and
"programmed" my flat file database program complete with simple programming
statements, field navigation, and reports. I have used external look up
tables and such - so I am not completely inept <g>.

I need advice on the EASIEST and fastest (to learn) relational database
program available over-the counter to consumers that can accommodate
scanning the pages of data right into the database.

Based on my description of my needs - can anyone suggest some software for
me????

Also, can someone please point me to some websites or other email lists that
may help me????

Thanks in advance for any and all assistance.....

Shelby   :-)

p.s. If you could cc me directly with your reply it would be helpful....

2. 0-byte database

3. How to convert flat file data into SQLSERVER 6.5

4. insert query with select

5. Importing flat file into relational files - Help?

6. Session ID determination

7. Can you convert relational record to flat record

8. ANN: jdance.com Dec 16 Java News

9. Accessing Flat files through DTS Flat File OLE DB Provider

10. Importing a flat file database in a relational one

11. Importing from flat text files to relational DB

12. Help: importing flat data into relational structure