Simon,
You must use multiple INSERT statements to insert multiple rows. The
exception to this is INSERT SELECT, which can be used to insert what is
returned by the SELECT statement.
Shane Witbeck
-----Original Message-----
From: Simon Halcrow [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 24, 2000 5:58 AM
To: CF-Talk
Subject: OT: SQL INSERTs across multiple related tables
Hi all
I've been trying to insert a record which spans multiple tables, i.e.
INSERT INTO
( SELECT Users.Surname, Wages.NINumber
FROM Users INNER JOIN Wages ON Users.User_ID =
Wages.User_ID
)
VALUES ('Bloggs', 'NY012398B')
and I keep getting Integrity violations; I have tried to do this in numerous
different ways (including creating the SELECT query in Access, then trying
to insert into that) - all sorts.
Anybody got any ideas on how I could accomplish this without having to
create 3 queries (first to insert into the Users table, second to pick up
the User_ID (autonumber), third to insert details (along with the User_ID
foreign key) into the NINumber table)?
Any help greatly appreciated.
Sapphire Technologies Ltd.
Secure in the Knowledge
Simon Halcrow Web Developer
E [EMAIL PROTECTED]
T +44 (0)1642 702100
F +44 (0)1642 702119
W www.sapphire.net
-----------------------------------------------
Any opinions expressed in this message are those of the individual and not
necessarily the company. This message and any files transmitted with it are
confidential and solely for the use of the intended recipient. If you are
not the intended recipient or the person responsible for delivering to the
intended recipient, be advised that you have received this message in error
and that any use is strictly prohibited.
Sapphire Technologies Ltd
http://www.sapphire.net
----------------------------------------------------------------------------
--------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
message with 'unsubscribe' in the body to [EMAIL PROTECTED]
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
