is it efficient to insert several table for executing 1 process?

tenma-tenma · #1 (**permalink**) 05-14-07, 11:12

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

gvee · #2 (**permalink**) 05-14-07, 11:36

Firstly, do not apologise for asking a "simple question"
Secondly, this is not a simple question!

Why can't you create a MR without any patients "attached" to it?

tenma-tenma · #3 (**permalink**) 05-14-07, 12:01

Hi, thank's for the fast reply.. =)

"Why can't you create a MR without any patients "attached" to it?"
MR is like an identification for each familiy, and each familiy consist of several patient..
does my erd is already correct? and if, for example, i want to add a new MR with new patients of course, so i have to do :

insert into havepatient(MRId, patientId) values (NULL, NULL);
insert into MR(MRId, regDate) values (1, 2007/04/05);
insert into patient(patientId, fname, lname) values(1, lala, baba);

is it correct? and is it efficient to perform 3 inserts just for adding 1 new MR?

thanks for your attention

gvee · #4 (**permalink**) 05-14-07, 17:24

Personally I'd advise you create the patients and MRs BEFORE you join them in your havepatient table

tenma-tenma · #5 (**permalink**) 05-14-07, 20:26

Hi...

yeah.. i should entered those 2 first..
thank's for your replies..

tenma-tenma · #6 (**permalink**) 05-15-07, 01:12

Hi..
sorry for asking the same problem againn..
so, i write something like this:

Code:

$queryMR= "INSERT INTO MR (MRId, time) values (NULL, current_timestamp())";
  $resultMR= safe_query($queryMR);

$queryPatient= "INSERT INTO patient ( patientId , fname ,lname)";
  $queryPatient.= "VALUES (NULL , '$firstname', '$lastname')";
  $resultPatient= safe_query($queryPatient);

$queryHave_Relation= "INSERT INTO havePatient(patientId, MRId) SELECT p.patientId, m.MRId";
  $queryHave_Relation.= "FROM patient p, MR m WHERE m.MRId='' and (p.fname= '$firstname' and p.lname= '$lastname')";
  $resultHave_Relation= safe_query($queryHave_Relation);

my question is what should i put in the MRId= '' (the red one), i want to take the result of MRId from $resultMR, but if i put the MRId straightaway (hardcode) then it is useless.. i have thought about taking the latest MRId from the MR table, but the problem is there are more than 1 user that perform insertion in the same time.

can anybody help me out from this problem? =)
thank's for ur attention..

gvee · #7 (**permalink**) 05-15-07, 03:47

One thing I noticed straight away is that you're insering a NULL value as your MRId!! You should NOT have null values for an identity field like this - and as your patientId! If these are autonumbers you can simply ignore them in an insert as they will be populated automatically.

Secondl; according to your 3rd query you are searching for patients' first names and last names as a means of joining the two... Bad move...
What if you have 10 people called John Smith?

tenma-tenma · #8 (**permalink**) 05-15-07, 04:43

should i use the combination of fname, lname, and DOB instead? i think it would be better if i could take the patientId from $queryPatient straightaway as the input for pID in $queryHave_Relation..

and what do you think would be the best for MRId?
thankss a lot..

gvee · #9 (**permalink**) 05-15-07, 07:53

Code:

INSERT
INTO patient(fname, lname) values('John', 'Smith', '01/01/1980 00:00:00.000');

INSERT
INTO patient(fname, lname) values('John', 'Smith', '01/01/1980 00:00:00.000');

INSERT
INTO patient(fname, lname) values('John', 'Smith', '01/01/1980 00:00:00.000');

There are now 3 John Smiths who happen to be born on the same day...
The whole point in an identity (key) field is to uniquely identify a record.

loquin · #10 (**permalink**) 05-15-07, 14:33

Also, after inserting a record, (for SQL Server) you may get the value of your most recently inserted value by using the @@identity function

Code:

INSERT INTO MR (time) values (current_timestamp())

SELECT TOP 1 @@identity as LastRecordID from MR

This will return the last identity value in your connection. Even if another users inserts a record after you've inserted the record, but before you select @@identity, the value you receive is the value associated with your insert.

gvee · #11 (**permalink**) 05-16-07, 05:06

Wow, I never knew you could do that...
Just a quickie on ths above loquin...
I ran the following in query Analyzer

Code:

SELECT TOP 1 @@identity AS LastRecordID FROM employee

Which gave me this error

Code:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

I'm running this on SQL Server 2k - I've never used a TOP statement in SS before and it appears that it's not liking them. Any ideas?

EDIT: Just ran this on a SS 2K5 database... the TOP worked, but it returns NULL every try so far..