Append query to ODBC linked table in SQL Server

[Sean Clayton]

Hi there,

I'm fighting an issue with my department's new database that has my
head hurting.

Here's the general layout: we just installed a new medical records
database with a web-based frontend, and a SQL Server backend. I've
created an ODBC linked Access database to SQL Server backend, and I'm
trying to update the linked tables with an append query. What I get is
a key violation error for every record I'm trying to append.

The query is fairly simple, it reads like this:

INSERT INTO dbo_Patients_copy ( PtMRN, PtLastName, PtFirstName )
SELECT People_unique_MRN.MRN, People_unique_MRN.LastName,
People_unique_MRN.FirstName
FROM People_unique_MRN;

dbo_Patients_copy does contain a single primary key field called
PatientId, which is an autonumber, although I'm not trying to append
anything to that field.

As I understand from reading the group, appending data to the table is
supposed to result in appended data with an appropriately incremented
autonumber, but violations is what I get instead.

Can someone tell what I'm doing wrong?

 

[Rick Brandt]

Hi there,

I'm fighting an issue with my department's new database that has my
head hurting.

Here's the general layout: we just installed a new medical records
database with a web-based frontend, and a SQL Server backend. I've
created an ODBC linked Access database to SQL Server backend, and I'm
trying to update the linked tables with an append query. What I get is
a key violation error for every record I'm trying to append.

The query is fairly simple, it reads like this:

INSERT INTO dbo_Patients_copy ( PtMRN, PtLastName, PtFirstName )
SELECT People_unique_MRN.MRN, People_unique_MRN.LastName,
People_unique_MRN.FirstName
FROM People_unique_MRN;

dbo_Patients_copy does contain a single primary key field called
PatientId, which is an autonumber, although I'm not trying to append
anything to that field.

As I understand from reading the group, appending data to the table is
supposed to result in appended data with an appropriately incremented
autonumber, but violations is what I get instead.

Can someone tell what I'm doing wrong?

What version of SQL Server? Older versions would sometimes have issues
where the seed value for the "Next IDENTITY" value gets out of whack and it
tries to re-use numbers that already exist in the table.

In any version if you modify the table the seed value might get reset to 1.

Can you enter records into the table by other means?

 

[Sean Clayton]

What version of SQL Server? Older versions would sometimes have issues
where the seed value for the "Next IDENTITY" value gets out of whack and it
tries to re-use numbers that already exist in the table.

In any version if you modify the table the seed value might get reset to 1.

Can you enter records into the table by other means?

The version is SQL Server 2005. I can manually enter data into the
linked table, and I can also enter data into the table in SQL Server's
Mnagement Studio. Seems to be just the append that gives troubles.

 

[Rick Brandt]

The version is SQL Server 2005. I can manually enter data into the
linked table, and I can also enter data into the table in SQL Server's
Mnagement Studio. Seems to be just the append that gives troubles.

Sometimes "Duplicate Key" errors can be caused by other unique indexes
rather than the primary key. Does the table have any other unique indexes
on it?

 

[Sean Clayton]

Sometimes "Duplicate Key" errors can be caused by other unique indexes
rather than the primary key. Does the table have any other unique indexes
on it?

Assuming that I'm reading this right (little rusty at this), two of
the fields that I'm trying to update have indices attached to them,
but they're marked Duplicates OK. The only field with a No Duplicates
index is the autonumber.

 

[Sean Clayton]

Assuming that I'm reading this right (little rusty at this), two of
the fields that I'm trying to update have indices attached to them,
but they're marked Duplicates OK. The only field with a No Duplicates
index is the autonumber.

*bump*

 

[Rick Brandt]

Try modifying your insert query so it only inserts a single record and see if
you get the same error. Are you quite sure you are not supplying a value for
the ID?

 

[Sean Clayton]

Try modifying your insert query so it only inserts a single record and see if
you get the same error. Are you quite sure you are not supplying a value for
the ID?

On your advice, I tried to run the query to update only one field.
Unfortunately, no success with any of the three, still get the same
key violation error. I went back and checked again to make triply sure
I wasn't trying to update the ID field...the ID field in the table I'm
trying to update is named PatientId, and I'm very sure that I'm not
trying to update it.

 

[Rick Brandt]

On your advice, I tried to run the query to update only one field.
Unfortunately, no success with any of the three, still get the same
key violation error. I went back and checked again to make triply sure
I wasn't trying to update the ID field...the ID field in the table I'm
trying to update is named PatientId, and I'm very sure that I'm not
trying to update it.

Are you updating or appending? I suggested appending a single record, not
updating a single field.

Are there any triggers on the table?

 

[Sean Clayton]

Are you updating or appending? I suggested appending a single record, not
updating a single field.

Are there any triggers on the table?

I'm sorry, I used the wrong word. I am trying to append, I just
modified the query I posted above for one field instead of the three.

Where would I look for triggers? I'm not familiar with that.

 

[Rick Brandt]

I'm sorry, I used the wrong word. I am trying to append, I just
modified the query I posted above for one field instead of the three.

Again, assuming you meant 3 records, not 3 fields here.

Where would I look for triggers? I'm not familiar with that.

On the server using Enterprise Manager or Management Studio depending on the
version of SQL Server you have. If there is a trigger that modifies or adds
records in additional table to could be those actions causing the error
(doubtful, but not impossible).


What I usually do when this is happening to me is display the datasheet view of
the append query and try copying and pasting that output into the source table
(one row at a time). Often that lets me isolate what the problem is.

 

[Sean Clayton]

Again, assuming you meant 3 records, not 3 fields here.


On the server using Enterprise Manager or Management Studio depending on the
version of SQL Server you have. If there is a trigger that modifies or adds
records in additional table to could be those actions causing the error
(doubtful, but not impossible).

What I usually do when this is happening to me is display the datasheet view of
the append query and try copying and pasting that output into the source table
(one row at a time). Often that lets me isolate what the problem is.

I'm sorry again...I need to slow down and read instead of just
scanning. My fault.

I tried what you said in this most recent post...copying and pasting
the output of the append query into the table, and it complained at me
when I didn't put anything in a pair of other fields...both of which
don't allow null values. I updated my source table to include those
fields and rewrote my query to include them in the append...and now it
works. I feel like a fool for not catching this sooner.

Thank you for your patience, sir...and for that nifty little query
trick, I've got to remember that one.

 

[Sean Clayton]

I'm sorry again...I need to slow down and read instead of just
scanning. My fault.

I tried what you said in this most recent post...copying and pasting
the output of the append query into the table, and it complained at me
when I didn't put anything in a pair of other fields...both of which
don't allow null values. I updated my source table to include those
fields and rewrote my query to include them in the append...and now it
works. I feel like a fool for not catching this sooner.

Thank you for your patience, sir...and for that nifty little query
trick, I've got to remember that one.

It seems like I've run into another problem, and I'm pretty stumped
here as well.

What I'm trying to do is to append data from Table A to Table B, based
on data in Table C. To be more specific, Table B and Table C have a
common field (PatientId), while Table A and Table B have a common
field (MRN). Table A contains records of patient data organized by
MRN. Table B contains the MRN as well as a autonumbered PatientId
field. Table C is the destination table, needing the values from Table
A organized by PatientId, but without MRN.

I know this is convoluted, but I'm hoping that someone might be able
to offer a little insight. The query I've built to attempt this
operation is as follows:

INSERT INTO Table C (PatientId, LabTest, LabResult)
SELECT Table B.PatientId, Table A.DataType, Table A.PSAvalue
FROM Table A, Table B INNER JOIN Table C ON Table B.PatientId = Table
C.PatientId
where Table A.MRN = Table B.PtMRN;

There are, as with my last problem, fields in the destination table
that cannot be left null, but I don't think that is the problem here.
I'm not getting any kind of validation errors, and the query seems to
run, but it only tells me that it's appending 0 rows, and I'm sure
that there are at least 2040.

Might someone have an idea on where I'm going wrong here?

 

[Sean Clayton]

It seems like I've run into another problem, and I'm pretty stumped
here as well.

What I'm trying to do is to append data from Table A to Table B, based
on data in Table C. To be more specific, Table B and Table C have a
common field (PatientId), while Table A and Table B have a common
field (MRN). Table A contains records of patient data organized by
MRN. Table B contains the MRN as well as a autonumbered PatientId
field. Table C is the destination table, needing the values from Table
A organized by PatientId, but without MRN.

I know this is convoluted, but I'm hoping that someone might be able
to offer a little insight. The query I've built to attempt this
operation is as follows:

INSERT INTO Table C (PatientId, LabTest, LabResult)
SELECT Table B.PatientId, Table A.DataType, Table A.PSAvalue
FROM Table A, Table B INNER JOIN Table C ON Table B.PatientId = Table
C.PatientId
where Table A.MRN = Table B.PtMRN;

There are, as with my last problem, fields in the destination table
that cannot be left null, but I don't think that is the problem here.
I'm not getting any kind of validation errors, and the query seems to
run, but it only tells me that it's appending 0 rows, and I'm sure
that there are at least 2040.

Might someone have an idea on where I'm going wrong here?

*slight bump*

 

[John W. Vinson]

If you are appending data INTO TableB, then you should NOT be including TableB
in the query; and you should not be inserting *into* TableC as your SQL does.

Could you rephrase your requirement, perhaps with real table names instead of
the artificial A B C? It sounds like you're saying one thing in your narrative
("append data from Table A to Table B") and doing something quite different in
your query ("INSERT INTO Table C...")

IF... pending your reevaluation... you're actually trying to match up data in
TableA and TableB and load that data into TableC, leave TableC out of the
query (except as the Append To target) and be sure you're joining TableA to
TableB:

INSERT INTO [Table C] (PatientId, LabTest, LabResult)
SELECT [Table B].PatientId, [Table A].DataType, [Table A].PSAvalue
FROM [Table A] INNER JOIN [Table B]
ON [Table A].MRN = [Table B].PtMRN;

John W. Vinson [MVP]

 

[Sean Clayton]

If you are appending data INTO TableB, then you should NOT be including TableB
in the query; and you should not be inserting *into* TableC as your SQL does.

Could you rephrase your requirement, perhaps with real table names instead of
the artificial A B C? It sounds like you're saying one thing in your narrative
("append data from Table A to Table B") and doing something quite different in
your query ("INSERT INTO Table C...")

IF... pending your reevaluation... you're actually trying to match up data in
TableA and TableB and load that data into TableC, leave TableC out of the
query (except as the Append To target) and be sure you're joining TableA to
TableB:

INSERT INTO [Table C] (PatientId, LabTest, LabResult)
SELECT [Table B].PatientId, [Table A].DataType, [Table A].PSAvalue
FROM [Table A] INNER JOIN [Table B]
ON [Table A].MRN = [Table B].PtMRN;

John W. Vinson [MVP]

I'll try to state it a little better...sorry I'm unclear, it's a
little tough to get my mind wrapped around.

Here's the query:
INSERT INTO dbo_LabTests ( PatientId, LabTest, LabResult )
SELECT dbo_Patients.PatientId, PSA_copy.DataType, PSA_copy.PSAvalue
FROM PSA_copy, dbo_Patients INNER JOIN dbo_LabTests ON
dbo_Patients.PatientId=dbo_LabTests.PatientId
WHERE PSA_copy.MRN=dbo_Patients.PtMRN;

I'm not sure at all if the query is saying what I'm trying to do
correctly, it may not be. I'm needing to append data to dbo_LabTests.
The PSA_copy table contains PSAs and patient medical record numbers
(MRNs) with the MRNs as the unique identifier. The table dbo_Patients
contains patient names with a unique number as the unique identifier,
as well as the patient MRNs.

I'm wanting to put the patients unique number from the dbo_Patients
table, along with the PSA data type identifier and the PSA value for
each patient from PSA_copy, into the dbo_LabTests table. The problem
is that PSA_copy has MRNs, dbo_Patients has the unique number and the
MRNs, and dbo_Labtests needs the unique number corresponding to the
dbo_Patients table, and not the MRNs.

Unless I'm wrong (and I could be), I think that I have to use all
three tables to get the data where I want it to go. I want to end up
with PSA data and the unique number in the dbo_LabTests table with the
unique number matching the unique number in the dbo_Patients table for
each patient, so the system can match them together.

 

[Sean Clayton]

If you are appending data INTO TableB, then you should NOT be including TableB
in the query; and you should not be inserting *into* TableC as your SQL does.

Could you rephrase your requirement, perhaps with real table names instead of
the artificial A B C? It sounds like you're saying one thing in your narrative
("append data from Table A to Table B") and doing something quite different in
your query ("INSERT INTO Table C...")

IF... pending your reevaluation... you're actually trying to match up data in
TableA and TableB and load that data into TableC, leave TableC out of the
query (except as the Append To target) and be sure you're joining TableA to
TableB:

INSERT INTO [Table C] (PatientId, LabTest, LabResult)
SELECT [Table B].PatientId, [Table A].DataType, [Table A].PSAvalue
FROM [Table A] INNER JOIN [Table B]
ON [Table A].MRN = [Table B].PtMRN;

John W. Vinson [MVP]

I'll try to state it a little better...sorry I'm unclear, it's a
little tough to get my mind wrapped around.

Here's the query:
INSERT INTO dbo_LabTests ( PatientId, LabTest, LabResult )
SELECT dbo_Patients.PatientId, PSA_copy.DataType, PSA_copy.PSAvalue
FROM PSA_copy, dbo_Patients INNER JOIN dbo_LabTests ON
dbo_Patients.PatientId=dbo_LabTests.PatientId
WHERE PSA_copy.MRN=dbo_Patients.PtMRN;

I'm not sure at all if the query is saying what I'm trying to do
correctly, it may not be. I'm needing to append data to dbo_LabTests.
The PSA_copy table contains PSAs and patient medical record numbers
(MRNs) with the MRNs as the unique identifier. The table dbo_Patients
contains patient names with a unique number as the unique identifier,
as well as the patient MRNs.

I'm wanting to put the patients unique number from the dbo_Patients
table, along with the PSA data type identifier and the PSA value for
each patient from PSA_copy, into the dbo_LabTests table. The problem
is that PSA_copy has MRNs, dbo_Patients has the unique number and the
MRNs, and dbo_Labtests needs the unique number corresponding to the
dbo_Patients table, and not the MRNs.

Unless I'm wrong (and I could be), I think that I have to use all
three tables to get the data where I want it to go. I want to end up
with PSA data and the unique number in the dbo_LabTests table with the
unique number matching the unique number in the dbo_Patients table for
each patient, so the system can match them together.

 

[Sean Clayton]

If you are appending data INTO TableB, then you should NOT be including TableB
in the query; and you should not be inserting *into* TableC as your SQL does.

Could you rephrase your requirement, perhaps with real table names instead of
the artificial A B C? It sounds like you're saying one thing in your narrative
("append data from Table A to Table B") and doing something quite different in
your query ("INSERT INTO Table C...")

IF... pending your reevaluation... you're actually trying to match up data in
TableA and TableB and load that data into TableC, leave TableC out of the
query (except as the Append To target) and be sure you're joining TableA to
TableB:

INSERT INTO [Table C] (PatientId, LabTest, LabResult)
SELECT [Table B].PatientId, [Table A].DataType, [Table A].PSAvalue
FROM [Table A] INNER JOIN [Table B]
ON [Table A].MRN = [Table B].PtMRN;

John W. Vinson [MVP]

I'll try to state it a little better...sorry I'm unclear, it's a
little tough to get my mind wrapped around.

Here's the query:
INSERT INTO dbo_LabTests ( PatientId, LabTest, LabResult )
SELECT dbo_Patients.PatientId, PSA_copy.DataType, PSA_copy.PSAvalue
FROM PSA_copy, dbo_Patients INNER JOIN dbo_LabTests ON
dbo_Patients.PatientId=dbo_LabTests.PatientId
WHERE PSA_copy.MRN=dbo_Patients.PtMRN;

I'm not sure at all if the query is saying what I'm trying to do
correctly, it may not be. I'm needing to append data to dbo_LabTests.
The PSA_copy table contains PSAs and patient medical record numbers
(MRNs) with the MRNs as the unique identifier. The table dbo_Patients
contains patient names with a unique number as the unique identifier,
as well as the patient MRNs.

I'm wanting to put the patients unique number from the dbo_Patients
table, along with the PSA data type identifier and the PSA value for
each patient from PSA_copy, into the dbo_LabTests table. The problem
is that PSA_copy has MRNs, dbo_Patients has the unique number and the
MRNs, and dbo_Labtests needs the unique number corresponding to the
dbo_Patients table, and not the MRNs.

Unless I'm wrong (and I could be), I think that I have to use all
three tables to get the data where I want it to go. I want to end up
with PSA data and the unique number in the dbo_LabTests table with the
unique number matching the unique number in the dbo_Patients table for
each patient, so the system can match them together.

 

[John W. Vinson]

I'll try to state it a little better...sorry I'm unclear, it's a
little tough to get my mind wrapped around.

Here's the query:
INSERT INTO dbo_LabTests ( PatientId, LabTest, LabResult )
SELECT dbo_Patients.PatientId, PSA_copy.DataType, PSA_copy.PSAvalue
FROM PSA_copy, dbo_Patients INNER JOIN dbo_LabTests ON
dbo_Patients.PatientId=dbo_LabTests.PatientId
WHERE PSA_copy.MRN=dbo_Patients.PtMRN;

I'm not sure at all if the query is saying what I'm trying to do
correctly, it may not be. I'm needing to append data to dbo_LabTests.
The PSA_copy table contains PSAs and patient medical record numbers
(MRNs) with the MRNs as the unique identifier. The table dbo_Patients
contains patient names with a unique number as the unique identifier,
as well as the patient MRNs.

I'm wanting to put the patients unique number from the dbo_Patients
table, along with the PSA data type identifier and the PSA value for
each patient from PSA_copy, into the dbo_LabTests table. The problem
is that PSA_copy has MRNs, dbo_Patients has the unique number and the
MRNs, and dbo_Labtests needs the unique number corresponding to the
dbo_Patients table, and not the MRNs.

Unless I'm wrong (and I could be), I think that I have to use all
three tables to get the data where I want it to go. I want to end up
with PSA data and the unique number in the dbo_LabTests table with the
unique number matching the unique number in the dbo_Patients table for
each patient, so the system can match them together.

Let's restate what your query is doing, as written:

You have dbo_Patients and PSA_Copy in a Cartesian join, no connection between
them. This will match every record in dbo_Patients with every record in
PSA_Copy; that is, if there are 200 patients and 30 records in PSA_Copy you'll
get all 6000 possible combinations.

You're now matching all these records to those records which *already exist*
in dbo_LabTests. Unless there is already a matching PatientID in the LabTests
table, you won't get anything appended.

You seem to be assuming that you must *select records from* dbo_LabTests by
including it in the query window in order to append records *into*
dbo_LabTests. That assumption is incorrect; you should generally NOT include
the "target" table in the query grid.

Remove dbo_LabTests from the query grid, and *JOIN* the MRN fields rather than
using the WHERE clause. This should work:

INSERT INTO dbo_LabTests ( PatientId, LabTest, LabResult )
SELECT dbo_Patients.PatientId, PSA_copy.DataType, PSA_copy.PSAvalue
FROM PSA_copy INNER JOIN dbo_Patients ON PSA_copy.MRN=dbo_Patients.PtMRN;


John W. Vinson [MVP]

 

[Sean Clayton]

Let's restate what your query is doing, as written:

You have dbo_Patients and PSA_Copy in a Cartesian join, no connection between
them. This will match every record in dbo_Patients with every record in
PSA_Copy; that is, if there are 200 patients and 30 records in PSA_Copy you'll
get all 6000 possible combinations.

You're now matching all these records to those records which *already exist*
in dbo_LabTests. Unless there is already a matching PatientID in the LabTests
table, you won't get anything appended.

You seem to be assuming that you must *select records from* dbo_LabTests by
including it in the query window in order to append records *into*
dbo_LabTests. That assumption is incorrect; you should generally NOT include
the "target" table in the query grid.

Remove dbo_LabTests from the query grid, and *JOIN* the MRN fields rather than
using the WHERE clause. This should work:

INSERT INTO dbo_LabTests ( PatientId, LabTest, LabResult )
SELECT dbo_Patients.PatientId, PSA_copy.DataType, PSA_copy.PSAvalue
FROM PSA_copy INNER JOIN dbo_Patients ON PSA_copy.MRN=dbo_Patients.PtMRN;

John W. Vinson [MVP]

Masterful!

It worked perfectly. Thank you, sir. You've just saved me a lot of
manual work.