473,231 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Data Load Query

Hi,

I'm extracting data from a mainframe application with a view to loading
it into a MS SQL database. I'm trying to determine the most efficient
way to format the mainframe extract file to make loading into the
database easier.

The problem I have is that the existing record structure includes an
array that can vary between 1 to 50. If I include this array in a
single record the table I use to import the data would need 50 columns
though not all these would be populated. There is a field in the record
to identify how many occurances of the array there are.

Current Record Structure :
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
Array :
Value1
Value2
Value3
....
up to Value50 (if required)

i.e.

12344,Mr Agent,$29.95,2,BX123,BX124
12345,Mr Jones,$14.95,3,XX123,XX124,XX125
12345,Mr Jones,$14.00,1,XY123
12345,Mr Jones,$15.95,2,XZ124,XZ125
12346,Mr Smith,$19.95,3,AX123,AX124,AX125
12346,Mr Smith,$19.00,1,BY123
12347,Mr Acant,$99.95,7,CX123,CX124,CX125,CX126,CX127,CX128 ,CX129

There may be up to 3 records created for each Account Number with
different values in the array fields.

Am I better to break this file into two files .. one with the core
customer information and a second file with a row for each array value
which has a link to the customer information file.
Or
Is there a way to efficiently process the original file once it is
loaded into the staging tables in the database ?

i.e.

File 1 - Core Customer Information
====================================
Current Record Structure :
Record Number
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated

File 2 - Array Information
====================================
Record Number
Array :
Value1
Value2
Value3
....
up to Value50 (if required)

File 1
========================
12344,Mr Agent,$29.95,2
12345,Mr Jones,$14.95,3
12345,Mr Jones,$14.00,1
12345,Mr Jones,$15.95,2
12346,Mr Smith,$19.95,3
12346,Mr Smith,$19.00,1
12347,Mr Acant,$99.95,7

File 2
========================
12344,BX123
12344,BX124
12345,XX123
12345,XX124
12345,XX125
12345,XY123
12345,XZ124
12345,XZ125
12346,AX123
12346,AX124
12346,AX125
12346,BY123
12347,CX123
12347,CX124
12347,CX125
12347,CX126
12347,CX127
12347,CX128
12347,CX129

At times the individual array values will be used for look ups though
essentially the Customer Information record will be the primary lookup
data.

I'm leaning toward changing my COBOL code and creating the 2nd output
unless someone can suggest a simple way to process the information once
loaded into the table.

Any help that could be suggested would be greatly appreciated.

Sep 21 '05 #1
1 1787
For a varying number of fields per record, you might consider XML, as
it's a good format for that kind of data. But I have to say that I have
very limited experience of importing XML data into MSSQL myself - check
out OPENXML in Books Online, the SQLXML tools from Microsoft (which
include an XML bulk load COM component), and you could also post in
microsoft.public.sqlserver.xml to get some feedback on that approach.

Simon

Sep 21 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Aaron | last post by:
I've been using MySQL for several months and enjoyed great success when installing other people's scripts to databses I make using phpMyAdmin. My challenge is developing from scratch the...
0
by: Montagna, Dan | last post by:
------_=_NextPart_001_01C35B55.62B4A6E0 Content-Type: text/plain; charset="iso-8859-1" Hello, I'm a very new mysql/php user and am trying to use the load data infile command without luck. I'd...
0
by: Donald Tyler | last post by:
Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to...
9
by: VMI | last post by:
We have this huge application that's based on storing tons of data on a dataTable. The only problem we're having is that storing LOTS of data (1 million records) into a datatable will slow down the...
2
by: Wysiwyg | last post by:
I was hoping to get some opinions on the efficiency of various methods of reusing the same dropdown list data. Here is the situation: Multiple panels on maintenance pages with TAB menus across...
2
by: johnny | last post by:
hi all, I wonder why this little script doesn't work, maybe it's the provider not allowing the use of load data infile ( I know some don't let users to run some tasks ), could you please tell me...
0
by: mjsterz | last post by:
I've been working with VB .NET for less than a year and this is the first time I've posted on one of these groups, so let me apologize beforehand if I'm being unclear, not posting my issue...
4
by: JIM.H. | last post by:
Hello, I am trying to write the data I got from a web service to my table in SQL Server I need to append the dataset wsDS to the dataset ds and do update. PVS.myWS.Loader load = new...
2
by: WuBin | last post by:
Hi, I have a GridVew and a sqlDtaSurce control in ASP.NET real-time page. The sqlDtaSurce control use a complicated query to load data from a SQL database. This real-time page is reloaded by...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.