Append query doesn't work

[sonso]

I have an append query where the two tables have the same structure, and have a single field primary key. I have manipulated the data so that there are not duplicate values. However when I run it (SQL below) I get the error message that there are key violations.

Is there a way to troubleshoot this?

INSERT INTO General_Ledger
SELECT DISTINCT Exp.*
FROM Exp

 

[johnnymac43]

Could it be a referential integrity problem? See Access Help for more info.

John

 

[williamu]

Hi,

I'd agree with John on this one. You are selecting all distinct fields from one table and trying to insert them into another. Since you'll also be getting the Primary Key from the SELECT statement chances are that the value already exists in the other table.

HTH

--
William
Software Engineer
ICQ No. 56047340

 

[sonso]

There is no referential integrity on the either table, and I am certain that the key values are distinct. The first table goes to 452, and the new table (to add) starts at 453 and ascends. Both are long integer.

 

[williamu]

Hi,

No you're missing the point here. You are selecting all distinct fields from the second table and inserting them into the first table. By its nature the primary key is unique in both tables but since you're selecting it 542 it will attemtp to insert 542 in to the other table.

To get this to work simply qualify the SQL SELECT with field name and use field names in the insert and exclude the Primary Key.

INSERT INTO TABLE1 (Col1, Col2, Col3) VALUES (SELECT Col1, Col2, Col2 FROM TABLE2);

HTH

--
William
Software Engineer
ICQ No. 56047340

 

[sonso]

I used the query builder in Access and got:

INSERT INTO General_Ledger ( ID, Row, DOCUMENT, [RULE CODE], ITEM, [DOC SEQ], [SEQ NUM], SERIAL, [DOC REFERENCE], [TRANS DATE], BANK, [POST PRD], SUBMISSION, REV, [NUMBER], SEQ, ACTION, [CMT TYPE], [BUDG OVRD], CHART, ACCI, FUND, ORGN, ACCT, PROG, [TRANSACTION AMOUNT], SIGN, [VENDOR/DESCRIPTION], [USER IDENTIFICATION], [DOC TYPE], [DOCUMENT DESCRIPTION], [Converted Amount] )

SELECT DISTINCT Exp.ID, Exp.Row, Exp.DOCUMENT, Exp.[RULE CODE], Exp.ITEM, Exp.[DOC SEQ], Exp.[SEQ NUM], Exp.SERIAL, Exp.[DOC REFERENCE], Exp.[TRANS DATE], Exp.BANK, Exp.[POST PRD], Exp.SUBMISSION, Exp.REV, Exp.NUMBER, Exp.SEQ, Exp.ACTION, Exp.[CMT TYPE], Exp.[BUDG OVRD], Exp.CHART, Exp.ACCI, Exp.FUND, Exp.ORGN, Exp.ACCT, Exp.PROG, Exp.[TRANSACTION AMOUNT], Exp.SIGN, Exp.[VENDOR/DESCRIPTION], Exp.[USER IDENTIFICATION], Exp.[DOC TYPE], Exp.[DOCUMENT DESCRIPTION], Exp.[Converted Amount]

FROM Exp;

The ID field is the primary key and there are no duplicates at all between the two tables and there is no autonumber. I thought I was selecting all distinct records and adding them.

 

[williamu]

Are you saying that this now works? As it stands I don't think it would.

--
William
Software Engineer
ICQ No. 56047340

 

[johnnymac43]

Are you sure that there are no relationships between your General_Ledger table and any other table, that would be absent for any of the records in your Exp table?

John

 

[sonso]

I'll check on the relationship thing. I wonder if it's similar to the error messages you get with Paradox and the BDE. In Pdox, if there's a field validation error when appending, the BDE reports it as Key Violation.

 

[sonso]

I was able to successfully run the query on two similar "import" tables. I removed the primary key from my main table (General_Ledger) but still got the same error message referencing Key Violations. Since there is no longer a primary key, I am now assuming that there is some sort of data validation violation. I've removed every default value, so this isn't the problem. I'm now moving on to exact matches on data types (since every record is being rejected, there must be a serious data-type mismatch...although I can't see it).