How to load data into table with foreign keys

[cuqsy0]

Hi there,

Think of we have two tables in a database. Table Sale has four columns:

---------------------------------------------
ReceiptID | SaleItem | SaleAmount | StoreID
---------------------------------------------

Table Store has two columns:

---------------------------------
StoreID | StoreName
---------------------------------

Assuming we load the data from flat text file(s). My question is how this text file(s)should be provided or is usually provided?

For example, is it in format A or B?

Format A:

FileA

StoreName, ReceiptID, SaleItem , SaleAmount

=============== =============== =============== ===

Format B:

File 1:
StoreID, StoreName

File 2:
ReceiptID | SaleItem | SaleAmount | StoreID

Certainly, there is almost no difficulty to load for files in format B. However, if we have a file in format A, it seems we have to first transform FileA into File1 and File2, and also need to determine the "StoreID". Think about if we have a lot of foreign keys in a table, the task of this transform looks very hard.

[ck9663]

If you are receiving a lot of Format A, you can still use it. Once you receive it, you know where it came from. You can load it into a temp table first and hardcode the StoreId. Then populate all the tables you need.

Happy Coding!!!


~~ CK

[developergarage]

search on net for
select 'identity' = @@IDENTITY;
hope it works :)

[cuqsy0]

search on net for
select 'identity' = @@IDENTITY;
hope it works :)

Thanks for the help. This is an interesting method although it seems not related to my question.

My question is how to input the values of foreign keys of a table, but MSDN's exmple (http://msdn.microsoft.com/en-us/library/ms187342.aspx) is on how to insert a value of a primary key of a table.

[Rabbit]

Use format b. But if you must use format a, the solution is simple.

1) Import the file.
2) Insert the distinct store names into a store table with an identity field.
3) Join the newly created table to the imported file to get the store id.

[cuqsy0]

Use format b. But if you must use format a, the solution is simple.

1) Import the file.
2) Insert the distinct store names into a store table with an identity field.
3) Join the newly created table to the imported file to get the store id.

Great! That is what I guess in the loading work of real world.

If I must use Format A, the work looks tough. If a table has 10 foreign keys, and each host table (to hold a primary key) for the corresponding foreign key has a lot of key identifiers to determine the primary key, I cannot image how much work needs to be done.

Anyway, thanks.