Inserting to Multiple Tables

[maggy0426]

Actually, I'd like to amend that a bit. IF you are using a database engine which supports update-able views, and your view meets the constraints the engine places on such views (like all fields present and a one-to-one relationship between the view and all the constituent tables, for example), then you *may* be able to accomplish it with one insert without using the trigger trick.

Now, whether Delphi 2010 supports that via its database components, I am not sure (I don't own 2010, so can't speak to it). However, it is a pointless exercise if your database engine doesn't support it.

Edit: Just as a sample, here are MySQL's restrictions on update-able views

Thanks for responding!!

Apparently, I also need help with the Insert SQL being new to all of this. I appreciate the site example but to be quite honest, it was above my head. I was told that inserting into multiple tables requires a join. I'm looking into this also.

[AthenaOfDelphi]

Hi,

Inserting into a table can be as simple as...

insert into mytable (mycolumn) values (1);

Obviously, it will get more complicated the more fields you have, but thats the essence of an insert statement in SQL. Murmandamus is spot on with his statements about updateable views and theoretically, Delphi should handle it because a view is a server side thing, so Delphi should just see it as another table. But, I'd like to add that it's not a good idea to update through views... even if you can, because as Mumandamus has pointed out, not every database engine supports it.

As he said, you need to insert the data in the correct order which will be governed by things like foreign keys and stuff (a foreign key is a reference in one table to the primary key, normally, in another table. They can sometimes have constraints on them to protect the integrity of the database, so you can't have a reference to a key value which doesn't exist for example).

The joins will come into play when you are reading the data.

Lets say you have two tables... table1 and table2, you want column1 from table1 and column2 from table2 and they are linked by another field, link1 which exists in both tables, the SQL would be something like this:-

select t1.column1,t2.column2 from table1 t1,table2 t2 where (t2.link1=t1.link1)

The t1 and t2 are aliases assigned to the tables to make it easier to type long statements. What this does is return column1 from table1, column2 from table2 where the link1 field in table2 matches that same field in table1. If you have a record in say table1 which doesn't have a record in table2 with the same link1 value, it will not be returned.

This is where you need a join. Lets say you want to return all the records in table1 and the corresponding records in table2 IF (and this is the important IF) there is a record. You'd need to use something like this:-

select t1.column1,t2.column2 from table1 t1 left outer join t2 on (t2.link1=t1.link1)

There is a right join, but as I understand it, in most cases, the default is a right join.

I hope that helps and doesn't confuse you too much :-) If you need more info, feel free to ask and I'll see if I can come up with an example or two to clarify the situation.

[Murmandamus]

Just a minor addition regarding types of joins: the primary dichotomy is between what are called inner and outer joins. Inner joins are more or less joins which return matching records which must exist in both "tables" (or "sides" of the join, really; they can also be sub-selects and stuff, but to keep it simple, think of two tables). Outer joins are where records from one don't have to match records in the other. This is where the "RIGHT" and "LEFT" join dichotomy layer comes into play. Basically, it allows you to say which table on the side of the join (LEFT or RIGHT) will have non-matching records pulled from it.

Here are some examples to differentiate them more clearly:

Table1: (fields are key|id_number|name)
A|123|Fred
B|765|Joe
D|901|Mack

Table 2: (fields are key|color|time)
A|Red|01:30
C|Blue|17:45
D|Cyan|10:11

Now, let's do an inner join: (explicit inner join)
SELECT Table1.key, Table1.name, Table2.color FROM Table1 INNER JOIN Table2 ON Table1.key=Table2.key
-or- (the implied inner join)
SELECT Table1.key, Table1.name, Table2.color FROM Table1, Table2 WHERE Table1.key=Table2.key

Here's what we would get: (fields are key|name|color)
A|Fred|Red
D|Mack|Cyan

Next, let's do a left outer join:
SELECT Table1.key, Table1.name, Table2.color FROM Table1 LEFT OUTER JOIN Table2 ON Table1.key=Table2.key

Here's what we would get: (fields are key|name|color)
A|Fred|Red
B|Joe|(null)
D|Mack|Cyan

Next, let's do a right outer join:
SELECT Table1.key, Table1.name, Table2.color FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.key=Table2.key

Here's what we would get: (fields are key|name|color)
A|Fred|Red
C|(null)|Blue
D|Mack|Cyan

[hr]

You can omit the "OUTER" keyword on an outer join, and just specify it "LEFT JOIN" or "RIGHT JOIN". "LEFT"/"RIGHT" are by default outer joins.

Seems simple, no? It can get quite complicated in short order when you are joining multiple tables and doing aggregate functions.

If you're really geeky about set theory, joins are different flavors of intersection.