JWColby
jwcolby at colbyconsulting.com
Mon Oct 30 20:24:18 CST 2006
There almost assuredly is a CRLF at the end of the last line. The whole job aborts, no lines are appended to the table. I am doing this with the "data import / export wizard". The odd part to my mind is that there are three new fields that are in there which will in the future be "match code" fields, which are allowed to be null, and which are indeed set to null. The fourth PKID field which is supposed to be an Integer / primary key, identity fails. My uneducated guess is that the entire batch is queued up or read in, then at the very end, SQL Server tries to actually start appending data. At which point it tries to stuff a null into this field, which by definition cannot be null, and since the "first record" that it attempts to actually create fails, the whole batch fails. I guess my question is, why is it trying to stuff a null into that field. I don't specify any data at all for that field, in fact that field does not even exist in the source csv file. My expectation is that SQL Server's identity widget would kick in and create the incrementing integer. Apparently this does not happen and since the field itself is not even specified the wizard tries to fill in the extra fields at the end with null values. It succeeds nicely with the extra fields which are text and can accept null values. I know this because if I try the same process without the PK field in the destination table, the data does successfully import. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com Sent: Monday, October 30, 2006 7:51 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Appending data to SQL Server table withidentity field You say that it fails at the very end. That suggests that maybe there is a CRLF on the last line, and the failure occurs on the "next" line. Does the whole job abort when it hits the failure, or do you get all the rows but the last one, or what? Are you doing this with DTS? ----- Original Message ---- From: JWColby <jwcolby at colbyconsulting.com> To: dba-sqlserver at databaseadvisors.com; Access Developers discussion and problem solving <accessd at databaseadvisors.com> Sent: Monday, October 30, 2006 7:10:47 PM Subject: [dba-SQLServer] Appending data to SQL Server table with identity field I am trying to append data from a CSV file to an existing table with a PK integer field (4 bytes) that is an identity, seed 1, increment 1. The append fails at the very end saying it can't append a null to the PK field. Does an identity not increment in the same manner that an autonumber does in Access? If so any ideas why this is failing? If not, any ideas how to cause this to happen. I hate to import to a table without the PK, and then append to the table with the PK. I have tried that and THAT does work. What is it about the import from CSV that is causing this failure? John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com