INSERTing Data

[Martyh]

I am entering huge amount of data (which was finally normalized) with SQL commands.

In this 1:M schema, the first “insert” is simple, but the next INSERT requires that I get the foreign key from the first INSERT.

What is the simplest way to do this?

TIA

 

[pr2-eugin]

Use the @@IDENTITY method.

 

[Martyh]

Thanks for the quick reply...

Can you elaborate?? I read through the entire post, and I'm still a little confused -- does @@IDENTITY work for ACE (or JET) and do you have a sample? I've never seen it at all!

 

[GinaWhipp]

Does the second import go to it's own table? If yes then just use an APPEND query grabbing the PK field as the FK in the second table.

 

[Martyh]

Does the second import go to it's own table? If yes then just use an APPEND query grabbing the PK field as the FK in the second table.

How do I "grab" the PK fields as it is an autonumber?

 

[GinaWhipp]

Are you creating a query to append the second table?

 

[Martyh]

Yes.... and I've found @@IDENTITY to give the right PK (to make the FK in the second table) Is that what you mean?

 

[GinaWhipp]

Well, not exactly but that might also work.

 

[Martyh]

Hi Gina,
I'm in the mood to learn ... What were you thinking?

 

[GinaWhipp]

Are you appending the data where the FK is to another table?

 

[Martyh]

Here is the schema:

(1) PK, PField1, PField2 ---> (Many) FK, FField1, FField2

The PK and the FK are related one to many. Simple...

How could a simple question like that have some many questions?

 

[GinaWhipp]

Then this should work...

INSERT INTO [Table2] ( FKField, Field2, Field3)
SELECT Table1.PKField, Table1.Field2, Table1.Field3
FROM Table1;

 

[Martyh]

Does this assume that Field data is order of the Table?? Can you give me the generic method for the insertation of data given two tables (1--> many)? Does this also work for Autonumbers?

 

[GinaWhipp]

The above is an example, you have to create the query so you will put it in *order* you want. The above is a generic method, I think perhaps you may think there is a method you an use across databases, there isn't becuase Field Names will not be the same. For the FK Autonumber will be Long and on the many side so sure what you are asking. Also, you cannot insert anything into an Autonumber in Access, it picks it's own. The only exception is if your data is on an SQL Server.