INSERT WITH relationships

Hi,

I've got two tables that i wish to insert data into simultaneously. The two tables have a relationship between the id on table1 and the field table1ID on table 2. the id field on table1 is an identity field and i wish to insert that into table2 [for several rows]

I can insert into table1 but not table2.

Does this make sense?

Kind Regards,
Rim:confused:Can't do it with one Statement

Table2 Can't be an Identity field as well

You will have to insert the Header(Table1) First then Details(Table2) After

When Inserting into identity field records I believe theres a system variable @.@.Identity that can identify the newly created id without having to do a re SELECT

U Can

Maybe pass the Insert Values into a Stored Proceedure that will complete the 2 steps for you

BEGIN
--Insert Header Row
END
@.MyId = (SELECT @.@.Identity)
BEGIN
--Insert Detail Rows with @.MyId
END

Type thingy

Hope this helps

GW|||Originally posted by GWilliy
Can't do it with one Statement

Table2 Can't be an Identity field as well

You will have to insert the Header(Table1) First then Details(Table2) After

When Inserting into identity field records I believe theres a system variable @.@.Identity that can identify the newly created id without having to do a re SELECT

U Can

Maybe pass the Insert Values into a Stored Proceedure that will complete the 2 steps for you

BEGIN
--Insert Header Row
END
@.MyId = (SELECT @.@.Identity)
BEGIN
--Insert Detail Rows with @.MyId
END

Type thingy

Hope this helps

GW

yeah that makes sense. i figured out the @.@.identity thing. It's good to get someone's opinion too rather that just the help files.

thank you very much.

Insert statements, primary keys and relationships

I have a number of tables (30+) all collecting info about societies. I have a primary key (soc_ID) in all my tables and a number of one-to-one relationships. All the tables are joined.

Q1 : If I add into the first row in one table, do I HAVE to insert the PK in EVERY other table its related to? Q2:is it wise to have foreign keys nullable?
My MS SQL SERVER statement::

insert into society(soc_id) values ('1');

ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Society_EDU_materials_targeted". The conflict occurred in database "mem_soc", table "dbo.EDU_materials_targeted", column 'soc_id'.
The statement has been terminated.

I understand the error. Is there a short way to insert the PK in all related tables automatically otherwise my insert statement will be HUGE.
This method seems REALLY long winded...I'm 99% sure that you've got the primary and foreign keys reversed between your EDU_materials_targeted and society tables. At least as I understand it, the soc_id should be the primary key in the society table, and it should be the foreign key in the dbo.EDU_materials_targeted table.

A foreign key should allow NULL values if the relationship has a cardinality of "1 to zero or more". In other words, if the child table might not have a matching row in the parent table, then the FK should allow NULL values.

Normally I'd move this discussion to the Microsoft SQL Server (http://www.dbforums.com/forumdisplay.php?f=7) forum, but since it still is a relatively pure SQL issue I'm Ok with leaving it here in the SQL forum.

-PatP

-PatP|||Thanks a lot! You're 100% correct!