PDA

View Full Version : Inserting to Multiple Tables



maggy0426
01-02-2011, 04:42 PM
Hello everyone, I am using Delphi2010 and I'm trying to do an insert into multiple tables. I don't know the best way to do this. What I'm wondering is if there is a possible way to do one insert using one of Delphi's tools like the TQuery or TClientDataSet or would it be better to use code (we use Pascal language). An array maybe? I haven't been using Delphi that long but I have inserted and updated info into one table before, not multiple. Also, these tables use pretty much the same field names.

Any help would be greatly appreciated.

Thanks in advance!!

Murmandamus
02-02-2011, 06:08 PM
Generally (ie, this isn't Delphi-specific), you can't insert into more than one table at a time*; what you do is insert into your independent tables first, then insert into your dependent tables after that. You can make this into an atomic operation via the use of transactions, but that isn't absolutely necessary in many cases.

For example, if you were developing an order application which used one table to store the non-line-item order data (like who is placing the order, addresses, billing, payment info, etc), and another table to store the line-item data (quantity, part number, description, price, etc), and you wanted to "insert" the order into your database/tables, then what you would do is insert the non-line-item record into its table first, then insert the line-item data into their table after that. This is because you'll likely have a dependency where the line-item records link back to a specific order in the non-line-item table and, thus, can't insert them until you insert the other record first.

On the surface, Database Views make it seem like you could do what you are wanting to do, but in reality, the underlying database engines won't support it. It is because Views are intended to pull and select data in an organized way, but they don't necessarily contain all of the fields for all of the tables they are selected from, which can be a serious problem if you tried to do an insert into one like that. So, to avoid the issue, and the complexity that would be required to resolve it, they just make the programmer do that work in the code.

* There is a trick that you can use in some cases where you can use an insert trigger on a table which, when a record is inserted into it, it then does the inserts on other tables as well; however, that trigger must have access to the data that you want to insert, so this trick may be of limited use.

Murmandamus
02-02-2011, 06:19 PM
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 (http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html)

maggy0426
02-02-2011, 07:23 PM
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 (http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html)


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
02-02-2011, 11:19 PM
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
04-02-2011, 08:38 AM
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. ;)