Inserting into two tables with one sql statement

[Greg Ofiesh]

Anyone who can help,

I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fields
K2 and F1. F1 is the foreign key relating to K1 and F2 is the foreign
key relating to K2.

My initial question has been how can I add a row to both tables with
accurate Fx values in one SQL INSERT statement? I have been told it
cannot be done.

If it can be done in one statement, what is that statement?

If it cannot be done in one statement, how can it be done in the least
number of statements?

Thanks,

Greg the SQL student

[Rolf Kemper]

Hallo Greg,

I'm not sure if I understood it right, but it sounds like that you
have 2 relationships between two tables. Did you really follow the
normalisation rules for relational databses ?
In SQL Server I always have created my IDs (Keys) automatically. Than,
after inserting a row into the first table, I gte the new ID by
functions provided in T-SQL e.g @@IDENTITY / SCOPE_IDENTITY ... The
new ID is used to insert a row in the second table.
Maybe you should put a smal example of what you are really doing here.
I also would be interested in an approch of inserting into multiple
related tables by one statement. As said, by today I do this
sequentially.
Regards
Rolf

gr********@yahoo.com (Greg Ofiesh) wrote in message news:<d9*************************@posting.google.c om>...

Anyone who can help,

I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fields
K2 and F1. F1 is the foreign key relating to K1 and F2 is the foreign
key relating to K2.

My initial question has been how can I add a row to both tables with
accurate Fx values in one SQL INSERT statement? I have been told it
cannot be done.

If it can be done in one statement, what is that statement?

If it cannot be done in one statement, how can it be done in the least
number of statements?

Thanks,

Greg the SQL student

[strider5]

gr********@yahoo.com (Greg Ofiesh) wrote in message news:<d9*************************@posting.google.c om>...

Anyone who can help,

I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fields
K2 and F1. F1 is the foreign key relating to K1 and F2 is the foreign
key relating to K2.

My initial question has been how can I add a row to both tables with
accurate Fx values in one SQL INSERT statement? I have been told it
cannot be done.

If it can be done in one statement, what is that statement?

If it cannot be done in one statement, how can it be done in the least
number of statements?

Thanks,

Greg the SQL student

hi there,

AFAIK ms-sql server supports neither deffered constraint checking nor
multiple assigment, it cannot be done in oe statement.

only workaround i can cuggest,
is make one of the foreign key columns nullable, say F1 in table T2,
insert into T2 (F1 is null), insert into T1 and then set appropriate
value
into column F1 (if your version supports "instead of" triggers i'd
look that way)

HTH
Strider

[Hugo Kornelis]

On 14 Sep 2004 17:03:57 -0700, Greg Ofiesh wrote:

Anyone who can help,

I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fields
K2 and F1. F1 is the foreign key relating to K1 and F2 is the foreign
key relating to K2.

My initial question has been how can I add a row to both tables with
accurate Fx values in one SQL INSERT statement? I have been told it
cannot be done.

If it can be done in one statement, what is that statement?

If it cannot be done in one statement, how can it be done in the least
number of statements?

Thanks,

Greg the SQL student

Hi Greg,

It's not possible to have one INSERT statement add data to two tables at
once. The minimum number of statements is, of course, two: one INSERT
statement for each table affected.

(Note - if you are really determined, you can pretend that it's done with
one statement by creating a view that joins both tables, creating an
INSTEAD OF INSERT trigger for that view that changes one INSERT statement
on the view to two INSERT statements for the underlying tables. But it's a
lot easier to just pop two INSERT statements into a stored procedure and
call that when you want to add data).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Greg Ofiesh]

Following normalization rules, T1 is a table with account info (e.g.-
username and password) while T2 identifies an entity. Since an entity
may not have an account, T1 stands alone. T2 identifies the entity by
its primary key and other tables use that key as their foreign key to
refer names, addresses, etc. to the entity. The dual relationship was
a suggestion for performance improvements.

The reason I asked the question is that to populate T2 with F1 I need
to know K1, and to populate T1 with F2 I need to know K2. It seemed to
me that SQL may have grown to the point of dealing with this, but
apparently not.

The problem is I need to populate T2 first, since it is the entity. T1
is optional. But if T2 only has K2 and F1, then how will I identify
the record that I just added to get K2 to populate F2 if I don't know
K2 and have no F1?

I have been told by others I need some type of unique identifier to
query the record to get K2. So I am now thinking thread ID along with
'1000000000' to keep from colliding with valid F1 entries. Then I can
get K2 and change F1 after populating T1.

Any thoughts are welcomed...

Greg

Ke*****@ee.nec.de (Rolf Kemper) wrote in message news:<bb*************************@posting.google.c om>...

Hallo Greg,

I'm not sure if I understood it right, but it sounds like that you
have 2 relationships between two tables. Did you really follow the
normalisation rules for relational databses ?
In SQL Server I always have created my IDs (Keys) automatically. Than,
after inserting a row into the first table, I gte the new ID by
functions provided in T-SQL e.g @@IDENTITY / SCOPE_IDENTITY ... The
new ID is used to insert a row in the second table.
Maybe you should put a smal example of what you are really doing here.
I also would be interested in an approch of inserting into multiple
related tables by one statement. As said, by today I do this
sequentially.
Regards
Rolf

gr********@yahoo.com (Greg Ofiesh) wrote in message news:<d9*************************@posting.google.c om>...

Anyone who can help,

I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fields
K2 and F1. F1 is the foreign key relating to K1 and F2 is the foreign
key relating to K2.

My initial question has been how can I add a row to both tables with
accurate Fx values in one SQL INSERT statement? I have been told it
cannot be done.

If it can be done in one statement, what is that statement?

If it cannot be done in one statement, how can it be done in the least
number of statements?

Thanks,

Greg the SQL student