SQL SERVER 2000
=============
I have a staging table (Staging) that I import text data into (a column named RecordData). From that table, I populate two other tables.
Here is an abbreviated layout of the import data and below is a sample of a couple of records:
1) acct number
2) name
3) feature 1 code
4) date feature 1 installed
5) cost of feature 1
6) feature 2 code
7) date feature 2 installed
8) cost of feature 2
9) feature 3 code
10) date feature 3 installed
11) cost of feature 3
12) more data...
I envision this as running in two passes. The first pass parses all 'fixed' data (account, name, 'more data'). The second pass would recurse parsing feature data.
In reality, there can be up to 75 occurances of 'features'. The records are all fixed lenght, which means there is space for 75 occurances even if no values.
I have the first part codes, where I load account, name, etc. into the first table (Accounts). I am trying to figure out how to efficiently load the second table (Features)
All three tables contain a column named DDE_ID. It is an identity column in the Staging table. It is carried forward to the Accounts table as the PK. There is a FeatureID column in the Features table and DDE_ID is a FK back to the Accounts table.
I need to iterate the input data up to 75 times, loading only those features that are not blank (once a blank has been found, iteration can break). For each record I insert into Features, I also need to load the appropriate DDE_ID value. The DDE_ID in Staging = DDE_ID in Accounts, so for this pahes, I do not require any reference to the Accounts table.
I've envisioned this a couple of ways, first inserting a single row containing the DDE_ID and first group of features for all rows in Staging that have at least one feature, then trying to create some kind of join logic and inserting from the 2nd-75th set of features, etc., but can't get in mind how to do this EFFICIENTLY (keyword).
This is going to be a nightly import and there can be thousands of rows.
Start Free Trial
