Why doesn't my insert query work?

[Zipster]

Though there are no errors I always get no rows affected.
What could be the reason for this?

INSERT INTO CONTHIST
(USERID, ACCOUNTNO, SRECTYPE, RECTYPE, ONDATE, ONTIME, ACTVCODE, RESULTCODE, STATUS, DURATION, UNITS, REF, NOTES, LINKRECID,
LOPRECID, CREATEBY, CREATEON, CREATEAT, LASTUSER, LASTDATE, LASTTIME, RECID)
SELECT CONTACT1.CREATEBY, CONTACT1.ACCOUNTNO, 'C' AS Expr1, 'CI' AS Expr2, CONTACT1.CREATEON, CONTACT1.CREATEAT, 'BRO' AS Expr5,
CONTACT2.USERDEF12, '0' AS Expr4, '0' AS Expr3, '0' AS Expr6, 'Brochure Request' AS Expr7, '' AS Expr8, '' AS Expr9, '' AS Expr10,
CONTACT1.CREATEBY AS Expr11, CONTACT1.CREATEON AS Expr12, CONTACT1.CREATEAT AS Expr13, CONTACT1.CREATEBY AS Expr14,
CONTACT1.CREATEON AS Expr15, CONTACT1.CREATEAT AS Expr16, CONTACT1.RECID
FROM CONTACT1 INNER JOIN
CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
WHERE (CONTACT1.CREATEBY <> NULL) OR (CONTACT2.USERDEF12 <> NULL)

http://www.k.abbott.btinternet.co.uk/

 

[jrbarnett]

Do you get any records shown when you run the Select From Where part of the query as a separate statement?

John

 

[Zipster]

Hi,

I've done just the select part like you said, no records appear. If I remove the WHERE part of the query then records are display but I don't want any records that are null in the CONTACT1.CREATEBY or the CONTACT2.USERDEF12, How do I filter it correctly?

Thanks in advance.

http://www.k.abbott.btinternet.co.uk/

 

[jrbarnett]

Well, that explains why no data was transferred - if there were no records matching the criteria.

Try the following last line modification:

WHERE (CONTACT1.CREATEBY IS NOT NULL) OR (CONTACT2.USERDEF12 IS NOT NULL)

 

[Zipster]

Excellent! Thanks very much John.

The only problem I've got now is this:

Cannot insert duplicate key row in object 'CONTHIST' with unique index 'CNHRECID'.

I haven't a clue what this means, is there anyway I can check for duplicates before inserting?

Thanks

http://www.k.abbott.btinternet.co.uk/

 

[jrbarnett]

Zipster
On the database, somebody has created a non duplicates allowed custom index called CNHREICID in the table CONTHIST. Transferring the data across would violate that particular rule, so Access won't let you do this.
If this isn't available from View -> Indexes in the table design view specifically, it is probably the primary key on the table.

Is there a way you can check for duplicates before inserting? Yes, you can either write VBA code to check that it only transfers records individually where the check is passed, or make an addition to the query's And statement using a NOT EXIST statement. I can't give it to you here because I don't know exactly what the index is defined as.

John

 

[Zipster]

Is this the defining code?:

CREATE UNIQUE
INDEX [CNHRECID] ON CONTHIST ([RECID])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]

If so, how do I use NOT EXIST to check when I'm inserting?

Thanks!

http://www.k.abbott.btinternet.co.uk/

 

[jrbarnett]

Hi Zipster,

Yes - the above code sets up an index called CNHRECID on the field RECID in the table Conthist.
It is just the one field that makes your index up.
What the extra line at the bottom of my code below does is checks to see if a record exists in which the RecID field in C1 (an alias to the CONTHIST table) exists in CONTACT1.RecID (which is the source table).

INSERT INTO CONTHIST
                      (USERID, ACCOUNTNO, SRECTYPE, RECTYPE, ONDATE, ONTIME, ACTVCODE, RESULTCODE, STATUS, DURATION, UNITS, REF, NOTES, LINKRECID, 
                      LOPRECID, CREATEBY, CREATEON, CREATEAT, LASTUSER, LASTDATE, LASTTIME, RECID)
SELECT     CONTACT1.CREATEBY, CONTACT1.ACCOUNTNO, 'C' AS Expr1, 'CI' AS Expr2, CONTACT1.CREATEON, CONTACT1.CREATEAT, 'BRO' AS Expr5, 
                      CONTACT2.USERDEF12, '0' AS Expr4, '0' AS Expr3, '0' AS Expr6, 'Brochure Request' AS Expr7, '' AS Expr8, '' AS Expr9, '' AS Expr10, 
                      CONTACT1.CREATEBY AS Expr11, CONTACT1.CREATEON AS Expr12, CONTACT1.CREATEAT AS Expr13, CONTACT1.CREATEBY AS Expr14, 
                      CONTACT1.CREATEON AS Expr15, CONTACT1.CREATEAT AS Expr16, CONTACT1.RECID
FROM         CONTACT1 INNER JOIN
                      CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
WHERE     (CONTACT1.CREATEBY IS NOT NULL) OR (CONTACT2.USERDEF12 IS NOT NULL) AND
          NOT EXISTS (SELECT C1.RECID FROM CONTHIST C1 WHERE C1.RECID = CONTACT1.RECID)

By the way, great website, your web development skills are certainly better than mine.

John

 

[Zipster]

Hi John,

I'm now using the following code:

INSERT INTO CONTHIST
(USERID, ACCOUNTNO, SRECTYPE, RECTYPE, ONDATE, ONTIME, ACTVCODE, RESULTCODE, STATUS, DURATION, UNITS, REF, NOTES, LINKRECID,
LOPRECID, CREATEBY, CREATEON, CREATEAT, LASTUSER, LASTDATE, LASTTIME, RECID)
SELECT CONTACT1.CREATEBY, CONTACT1.ACCOUNTNO, 'C' AS Expr1, 'CI' AS Expr2, CONTACT1.CREATEON, CONTACT1.CREATEAT, 'BRO' AS Expr5,
CONTACT2.USERDEF12, '0' AS Expr4, '0' AS Expr3, '0' AS Expr6, 'Brochure Request' AS Expr7, '' AS Expr8, '' AS Expr9, '' AS Expr10,
CONTACT1.CREATEBY AS Expr11, CONTACT1.CREATEON AS Expr12, CONTACT1.CREATEAT AS Expr13, CONTACT1.CREATEBY AS Expr14,
CONTACT1.CREATEON AS Expr15, CONTACT1.CREATEAT AS Expr16, CONTACT1.RECID
FROM CONTACT1 INNER JOIN
CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
WHERE (CONTACT1.CREATEBY IS NOT NULL) AND (NOT EXISTS
(SELECT C1.RECID
FROM CONTHIST C1
WHERE C1.RECID = CONTACT1.RECID))

Realised I don't need to check USERDEF12 but I'm still getting:

Cannot insert duplicate key row in object 'CONTHIST' with unique index 'CNHRECID'.

By the way your database skills are far better than mine!
If you need ANY help with web related stuff, don't hesitate to ask.

Thanks!

http://www.k.abbott.btinternet.co.uk/

 

[jrbarnett]

Kevin,

Try this modification. Not nearly as quick but it should work.

WHERE (CONTACT1.CREATEBY IS NOT NULL) AND (RECID NOT IN
(SELECT DISTINCT C1.RECID
FROM CONTHIST C1)

By the way my website is

http://www.jrbarnett.freeserve.co.uk

John

 

[Zipster]

Sorry John still getting the same error.

http://www.k.abbott.btinternet.co.uk/

 

[Rolliee]

Is one of the values you are trying to insert an autonumber. If so that is a no no.

rollie@bwsys.net

In the US of A - Illinois