I'm not seeing why it happens, but you could simplify your code: <cfquery datasource="Intranet"> INSERT INTO barcodes (Barcode, ItemID) SELECT Barcode, ItemID FROM stockitemdetails </cfquery>
Pascal > -----Original Message----- > From: James Smith [mailto:[EMAIL PROTECTED] > Sent: 03 November 2004 14:01 > To: CF-Talk > Subject: Odd SQL Behaviour, errors generated by values I am not trying to > use! > > I am looping over a result set to insert the results into a new table. > > Select Query > ------------ > <cfquery name="Select2" datasource="Intranet"> > SELECT Barcode, ItemID, Title > FROM stockitemdetails > ORDER BY Barcode > </cfquery> > > Insert Query > ------------ > <cfloop query="Select2"> > <cfoutput>#Select2.ItemID# - #Select2.Barcode# - > #Select2.Title#</cfoutput><br> > <cfquery name="Insert1" datasource="Intranet"> > INSERT INTO barcodes (Barcode, ItemID) > VALUES (#Select2.Barcode#,#Select2.ItemID#) > </cfquery> > </cfloop> > > It should also be noted that the barcodes table includes an ID column > which > is an Auto Increment primary key, and the barcode column is INT(13) > zerofill > indexed as unique. > > When this template is executed the first 4 values are inserted correctly, > however the 5th inserts the barcode wrong (as 0004294967295 where the > output > is 0008811171629) and the 6th then errors by trying to insert this same > wrong value again even though the output is unique (0008811953928). The > error generated is... > > Invalid argument value: Duplicate entry '0004294967295' for key 2 > > However the error box lists the SQL statement in question as... > > INSERT INTO barcodes (Barcode, ItemID) VALUES (0008811953928,17870) > > Could anyone tell me why the number 0004294967295 is causing an error when > the SQL statement doesn't even show it being used? > > NOTE: If I remove the requirement for the barcode column to be unique then > almost all of the barcodes are inserted as this same wrong number, and by > almost all I mean only 20 or so work correctly out of about 10,000 total > rows. > > -- > James Smith > [EMAIL PROTECTED] > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=35 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183249 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
