Copy ID from one table to another as foreign key

Hi,
I have two tables, one, called tblDoctor, with
docNo, docName . . .
the other, called tblPatient with
pntNo, pntName . . .
I want to insert a specific docNo value from one row in tblDoctor into
a specific row in tblPatient using a stored procedure within ADO.
Can i have an SQL statement along the lines of:
INSERT INTO tblPatient (docNo)
WHERE pntName = "a value"
SELECT docNo
FROM tblDoctor WHERE docName = "another value"
If not, has anyone got any suggestions on alternative methods of
accomplishing the same thing?
ThanksTry,
update tblPatient
set docNo = (SELECT docNo FROM tblDoctor WHERE docName = "another value")
WHERE pntName = "a value"
Be sure that you get just one docNo for a specific docName, if not sql
server will give an error. A trick can be to use "TOP 1" in the select
statement, but this is not the correct way to do this.
AMB
"Assimalyst" wrote:

> Hi,
> I have two tables, one, called tblDoctor, with
> docNo, docName . . .
> the other, called tblPatient with
> pntNo, pntName . . .
> I want to insert a specific docNo value from one row in tblDoctor into
> a specific row in tblPatient using a stored procedure within ADO.
> Can i have an SQL statement along the lines of:
> INSERT INTO tblPatient (docNo)
> WHERE pntName = "a value"
> SELECT docNo
> FROM tblDoctor WHERE docName = "another value"
> If not, has anyone got any suggestions on alternative methods of
> accomplishing the same thing?
> Thanks
>|||I've guessed what your tables should look like from your question and I used
some artistic licence to add a docNo Foreign Key to the Patient table. That
is of course that I understand what you are trying to achieve.
CREATE TABLE tblPatient (patientNo int, pntName varchar(10), docNo int)
CREATE TABLE tblDoctor (docNo int, docName varchar(10))
UPDATE tblPatient SET DocNo = D.docNo
FROM (SELECT docNo FROM tblDoctor WHERE docName = 'another value') D
WHERE pntName = 'a value'
Hope this offers some thought to your problem
Andy
"Assimalyst" wrote:

> Hi,
> I have two tables, one, called tblDoctor, with
> docNo, docName . . .
> the other, called tblPatient with
> pntNo, pntName . . .
> I want to insert a specific docNo value from one row in tblDoctor into
> a specific row in tblPatient using a stored procedure within ADO.
> Can i have an SQL statement along the lines of:
> INSERT INTO tblPatient (docNo)
> WHERE pntName = "a value"
> SELECT docNo
> FROM tblDoctor WHERE docName = "another value"
> If not, has anyone got any suggestions on alternative methods of
> accomplishing the same thing?
> Thanks
>|||Thank you for the replies, i've tried both suggestions, but having no
luck. The tblPatient.doctorNo remains blank.
Below is the relevant code and stored procedure, i'm getting no errors,
and the code runs through the debugger without a hitch.
string strSgyNo = System.Convert.ToString(intSgyNo);
string strDocFName = fNameTxtBx.Text;
string strDocLName = lNameTxtBx.Text;
string strPatientCode = patientCodeLbl.Text;
string strDOB = dobLbl.Text;
// Use stored procedure to input doctorNo as foreign key in tblPatient
SqlCommand cmd = new SqlCommand("proc_AddDocToPnt", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Add parameters to command, which will be passed to the stored
procedure
cmd.Parameters.Add(new SqlParameter("@.docFName", strDocFName));
cmd.Parameters.Add(new SqlParameter("@.docLName", strDocLName));
cmd.Parameters.Add(new SqlParameter("@.sgyNo", strSgyNo));
cmd.Parameters.Add(new SqlParameter("@.pntCode", strPatientCode));
cmd.Parameters.Add(new SqlParameter("@.pntDOB", strDOB));
Stored Procedure:
CREATE PROCEDURE proc_AddDocToPnt
(@.docFName varchar(20),
@.docLName varchar(30),
@.sgyNo int,
@.pntCode varchar(15),
@.pntDOB datetime
)
AS
UPDATE tblPatient
SET doctorNo = D.doctorNo
FROM(SELECT doctorNo
FROM tblReferringDoctorContacts
WHERE (docFName = @.docFName)
AND (docLName=@.docLName)
AND (sgyNo=@.sgyNo)) D
WHERE ((pntUnitID = @.pntCode) AND (pntDOB = @.pntDOB))
GO
Can anyone spot a problem?
Thanks again.|||Hi
Run these 2 queries. Both should return results using Query Analyser
DECLARE @.docFName varchar(20)
DECLARE @.docLName varchar(30)
DECLARE @.sgyNo int
DECLARE @.pntCode varchar(15)
DECLARE @.pntDOB datetime
--Set the values that would be passed using your code
SET @.docFName = ''
SET @.docLName ''
SET @.sgyNo = 0
SET @.pntCode ''
SET @.pntDOB '1980-01-01'
SELECT doctorNo
FROM tblReferringDoctorContacts
WHERE (docFName = @.docFName)
AND (docLName=@.docLName)
AND (sgyNo=@.sgyNo)
SELECT * FROM tblPatient WHERE ((pntUnitID = @.pntCode) AND (pntDOB =
pntDOB))
If you don't get any results from either query, then the query to update the
tblPatient will results in no changes being made because 1 or both of the
criterion isn't matched.
Using Query Analyser (included with MS SQL2K) or Visual Studio to test your
procedure / queries may offer more information from the output of your
queries better than running from your code.
Good Luck :)
Andy Furnival
"Assimalyst" wrote:

> Thank you for the replies, i've tried both suggestions, but having no
> luck. The tblPatient.doctorNo remains blank.
> Below is the relevant code and stored procedure, i'm getting no errors,
> and the code runs through the debugger without a hitch.
> string strSgyNo = System.Convert.ToString(intSgyNo);
> string strDocFName = fNameTxtBx.Text;
> string strDocLName = lNameTxtBx.Text;
> string strPatientCode = patientCodeLbl.Text;
> string strDOB = dobLbl.Text;
> // Use stored procedure to input doctorNo as foreign key in tblPatient
> SqlCommand cmd = new SqlCommand("proc_AddDocToPnt", conn);
> cmd.CommandType = CommandType.StoredProcedure;
> // Add parameters to command, which will be passed to the stored
> procedure
> cmd.Parameters.Add(new SqlParameter("@.docFName", strDocFName));
> cmd.Parameters.Add(new SqlParameter("@.docLName", strDocLName));
> cmd.Parameters.Add(new SqlParameter("@.sgyNo", strSgyNo));
> cmd.Parameters.Add(new SqlParameter("@.pntCode", strPatientCode));
> cmd.Parameters.Add(new SqlParameter("@.pntDOB", strDOB));
>
> Stored Procedure:
> CREATE PROCEDURE proc_AddDocToPnt
> (@.docFName varchar(20),
> @.docLName varchar(30),
> @.sgyNo int,
> @.pntCode varchar(15),
> @.pntDOB datetime
> )
> AS
> UPDATE tblPatient
> SET doctorNo = D.doctorNo
> FROM(SELECT doctorNo
> FROM tblReferringDoctorContacts
> WHERE (docFName = @.docFName)
> AND (docLName=@.docLName)
> AND (sgyNo=@.sgyNo)) D
> WHERE ((pntUnitID = @.pntCode) AND (pntDOB = @.pntDOB))
> GO
> Can anyone spot a problem?
> Thanks again.
>|||Thanks again,
I've run that, it seems it is finding the doctorNo from
blReferringDoctorContacts, but is not ablke to find the row from
tblPatient because the insetrts are not working properly.
If the number entered into the form is say 19/04/1945, the database
stores the date as 19/04/2001? Always changes the year to 2001!'
Very strange, but hence it cannot find the record. Think i'll need to
sort this before proceding with the above.
Thanks again|||OK solved that, i'd declared the pntDOB ad a varchar instead of a
datetime in the stored procedure oops!
Since tried again though, and i'm still not getting a row selected from
tblPatient.
i've set @.pntCode = 'CO123' and @.pntDOB = '10/03/1950' a row exists in
the tblPatient where pntUnitID = CO123 and pntDOB = 10/03/1950, but it
is not being found any ideas why?
Thanks again|||The problem seems to be with the date, although it is displayed in the
table as 10/03/1950 it is actually stored as 1950-03-10. if i change
@.pntDOB to 1950-03-10 it works. :)|||Dates in SQL can be ambigious. Therefore 10/03/1950 could be interpreted as
either 10-March-1950 or 3-October-1950.
When using datatimes in sql use the data format yyyy-mm-dd so set your
@.pndDOB = '1950-03-10'
To see how your dates are being interpreted run the code with your @.pndDOB
set.
CONVERT(SMALLDATETIME, @.pndDOB, 112) -- 112 is the yyyy-mm-dd format
Thanks
Andy
"Assimalyst" wrote:

> OK solved that, i'd declared the pntDOB ad a varchar instead of a
> datetime in the stored procedure oops!
> Since tried again though, and i'm still not getting a row selected from
> tblPatient.
> i've set @.pntCode = 'CO123' and @.pntDOB = '10/03/1950' a row exists in
> the tblPatient where pntUnitID = CO123 and pntDOB = 10/03/1950, but it
> is not being found any ideas why?
> Thanks again
>|||Thank you again.
Forgive my stupidity but i'm not sure how to implement what you're
suggesting.
Do you mean to change the query SET to
SET @.pntDOB = CONVERT(SMALLDATETIME, @.pntDOB, 112)
I've tried this, and get now matched rows. I've also tried
SET @.pntDOB = CONVERT(SMALLDATETIME, 10/03/1950, 112)
and again get no matches. the only way i've found so far is to manually
type
SET @.pntDOB = '1950-03-10' but i really need to implement this convert.
Can you elaborate?
Thankssqlsql