hi..

sorry, if i ask a simple question but it quite confusing me anyway..
so i have 3 table..
MR(MRId->pk, regDate)
patient(patientId->pk, fname, lname)
havepatient (MRId ->pk, patientId->pk)

my question is, if i want to add new MR, in which 1 MR can has several patients, it means that i have to execute3 different insert commands for both these 3 tables?

please reply me as soon as possible =)
thx for your attention

maybe, maybe not

depends on your DB server. Some versions will allow inserts into multiple tables in a single query

INSERT ALL INTO table1 (fieldlist1) VALUES(valuelist1) INTO table2 (fieldlist2) VALUES(valuelist2) ;

Chris - does that still fire off three inserts behind the scenes? Somehow I get the feeling that type of syntax is really more about easy SQL than efficiency inside the db engine?

Moccacinoe - What you're doing actually is pretty efficient. Databases are meant to do exactly what you're talking about, and optimized in many ways to get the job done more quickly. But consider the alternative; you could put everything in one table with lots more fields, and repeat the MR on every row. Then when you need to change, say, a last name, you might need to update several thousand rows instead of one. When you need to do a particular query, say to get the average of whatever, you'll be able to write queries that read in far less data, because the data is divided up between three tables. In a lot of different scenarios, accessing a smaller part of your data ( because you broke it up into relational tables ) is much faster than accessing all of it.

Three inserts might sound bad, but they're the price you pay for all this efficiency down the line.

Originally Posted by chrishirst

INSERT ALL INTO table1 (fieldlist1) VALUES(valuelist1) INTO table2 (fieldlist2) VALUES(valuelist2) ;

That's so cool. I never knew you could mix tables like that! Thanks.

Chris - does that still fire off three inserts behind the scenes? Somehow I get the feeling that type of syntax is really more about easy SQL than efficiency inside the db engine?

Never had the chance to test it on a real DB it's an Oracle syntax as I recall. MySql doesn't support it and I've never bothered to try it in MS, it's as easy to write a SP and fire the data to that.

Ahhh, Oracle. They let you write queries like "Select * From A Join B Using xID" ... instead of "Select * From A Join B On A.xID = B.xID" Their SQL dialect is a lot more featured, letting people do things like store the data in two tables pre-joined on disc. I haven't had much opportunity to work with Oracle, but whenever I have, I've been amazed at all of what's available.

Oracle is way too complex. SQL Server really strikes the perfect balance between use and complexity. Seriously, we're talking like 40 or 50 keywords compared to like 400 or 500.

Hi,

thx for your replies..
i just read the forum again =)
thx..

regards,


Lena