Import Design Question

I have at least three tables into which the user can import data.
The three tables are 1) Portfolio 2) Account 3) Transactions. The user
will provide us three files - one for each entity (in the file) related
by a userdefined NUMBER field.
These three tables are linked by foreign keys but the key columns in
the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
TRANSACTIONID).
The files can be really big so the import has to be quick. Right now I
first insert the rows into PORTFOLIO, get the generated IDs , loop
through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
ID and then import the TRANSACTIONS. This process of retreiving and
setting the IDs is consuming a long time and making the import very
slow. I am wondering if using IDENTITY is the right design for the
above mentioned tables. Is it better to generate the key values myself
using a SEED table?
Is there a more elegant solution to the problem?
Thanks.Looping? In SQL?
Don't you have any natural keys in the source files? If there is a natural
key (or at least one or more candidate columns) use that to achieve a
set-based solution.
Whether you use identity or any other kind of key generation is IMHO
irrelevant, since there really should be a more natural way to uniquely
identify each row of data (i.e. a natural relationship between the sets).
ML
http://milambda.blogspot.com/|||S Chapman wrote:
> I have at least three tables into which the user can import data.
> The three tables are 1) Portfolio 2) Account 3) Transactions. The user
> will provide us three files - one for each entity (in the file) related
> by a userdefined NUMBER field.
> These three tables are linked by foreign keys but the key columns in
> the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
> TRANSACTIONID).
> The files can be really big so the import has to be quick. Right now I
> first insert the rows into PORTFOLIO, get the generated IDs , loop
> through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
> ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
> ID and then import the TRANSACTIONS. This process of retreiving and
> setting the IDs is consuming a long time and making the import very
> slow. I am wondering if using IDENTITY is the right design for the
> above mentioned tables. Is it better to generate the key values myself
> using a SEED table?
> Is there a more elegant solution to the problem?
> Thanks.
>
Sounds like your relationships are like this:
Portfolio -> AccountID -> TransactionID
meaning a single portfolio links to multiple Account ID's, a single
account links to multiple Transaction ID's.
You might consider changing this. Look for natural keys to link on
instead of manufacturing one (using an ID value). For instance, a
transaction should link to an account via ACCOUNT NUMBER. A portfolio
should contain multiple ACCOUNT NUMBERS, not account ID's.
What this will allow you to do is to bulk import all three tables
without having to mess with finding ID's, updating ID's, etc. The data
is already naturally linked together.|||Unfortunately there are no natural keys. The Portfolio Number and
Account Number can be gauranteed to be unique only within a batch.
Also, I am NOT looping through in Sql, looping through rows in the
dataset inside the program.
Tracy McKibben wrote:
> S Chapman wrote:
> Sounds like your relationships are like this:
> Portfolio -> AccountID -> TransactionID
> meaning a single portfolio links to multiple Account ID's, a single
> account links to multiple Transaction ID's.
> You might consider changing this. Look for natural keys to link on
> instead of manufacturing one (using an ID value). For instance, a
> transaction should link to an account via ACCOUNT NUMBER. A portfolio
> should contain multiple ACCOUNT NUMBERS, not account ID's.
> What this will allow you to do is to bulk import all three tables
> without having to mess with finding ID's, updating ID's, etc. The data
> is already naturally linked together.

Import and Export Wizard: Did they try it out before they shipped it!!!?

I have an excel file that, at least to me, is pretty simple. It's got 8 tables with about 25 records each. I'm trying to import 2 of those tables. Everytime I try to import I get this message:

Column "Name" cannot convert between unicode and non-unicode string data types.

So I've tried every combination of import/export that I can think of here and stil I get this message. Now I really don't think that I have any oddball characters in my data. As a matter of fact there isn't even any punctuation in there. And yet, SQL thinks there's some unicode in there. My dest column type for Name is a varchar(max). No issues there. So this is really about the simplest sort of data transfer that I can think of.

And yet it doesn't work. Which leads me to my question: did anyone at the big M actually use this wizard before they shipped it? No really. I'm doing a very simple data transfer and I'm gonna have to resort to some custom code to import my data? I've successfully imported data into the database, but I had to do it one table at a time (csv), because of the above unicode issue. I have foreign key constraints here, so I'm gonna have to import all this data in a very specific order, one table at a time.

One really absurd oversight is the names of the tables. Excel seems to save table names with a $ after. But the wizard doesn't seem to understand that. So every time I have to tell it that the Company$ table in excel actually needs to point to the Company table in SQL. Huh.

I could've saved myself some time if that I/O wizard just wasn't there.

One more thing, did they think that I might want to import more than one table to the same database via a csv file? No, they didn't because the wizard, once completed, only gives you the option of exiting. You can't hit "Back" and do the same thing with a different flat file. You have to restart the wizard and re-connect to the database etc. etc. Start from scratch. So I'm wasting even more time because of this thing.

|||

Wait, it gets worse!!!

Now I'm trying to export the database to an excel file and I'm getting the same message!

How can an export wizard not know how to export varchar fields? Possibly the second most common field type, right? No, they aren't nvarchars, they're varchars.

Now I'm gonna have to custom code this ***. Just so I can export a freakin varchar field.

|||

Can you supply some more specifics? I'm not exactly a MS lover, but I have used the Import/Export wizard on a wide variety of data sources and have seldom been totally stymied. I have encountered some difficulties with Excel imports, mostly around fields being truncated due to formatting.

Can you isolate the data that it is barfing on? It is also possible to specify the output type. You can also adjust the query used to select the data.

Repeating an import/export is also a pet peave of mine, but it is possible to save the definition as a DTS package and get reuse there. Also, the list boxes are good at remembering your previous choices, so the repetition can be slightly less tedious.

Like I said, I'm usually the first to bash the products that MS has "discovered" from others, but Import/Export is a place I usually give them their props.

|||

Here's a list of more problems I'm encountering:

- wizard is locking the excel file so that I can't edit it, sometimes locking it regardless of where I am in the process of importing (even locked on the pick a file screen sometimes)

- wizard is encountering truncation errors on fields that are not included in the import (marked as <ignore> in field mapping)

- encountering a truncation error on a column that is defined as nvarchar(max) in the database

The bottom line: I'm giving up on using this wizard with an excel file. After changing every text field in the database to nvarchar(max) I'm still not able to import fields that have a grand total of one character in them due to truncation errors.

There is simply nothing special about this data. No symbols. It's all the keys that you see on your keyboard and not even the ones above the numbers (!@.#$ etc). I've blown too much time trying to get this wizard to do something extremely simple.

|||

anomolous wrote:

Can you isolate the data that it is barfing on? It is also possible to specify the output type. You can also adjust the query used to select the data.

Repeating an import/export is also a pet peave of mine, but it is possible to save the definition as a DTS package and get reuse there. Also, the list boxes are good at remembering your previous choices, so the repetition can be slightly less tedious.

Like I said, I'm usually the first to bash the products that MS has "discovered" from others, but Import/Export is a place I usually give them their props.

It's barfing on any text field.

I think the DTS package save will only work for specific values, right? Like I'd really like to be able to just hit <BACK> a few times, change the flat file that I'm using then hit forward and so on. Without having to re-select the db and password. Of course this would all be a moot point if I could get the freakin excel import to work.

Reply if you want to, but I've decided using this wizard to import an excel file is a waste time. Now I get to use flat files, which means I have to go through this wizard 12 freakin times.

And this textbox sucks too. I just inserted a smily and it deleted half of a paragraph!

|||Don't use the excel import unless you have a lot of time on your hands, that's the answer!