artful at rogers.com
artful at rogers.com
Mon Oct 30 22:09:41 CST 2006
So you have tthree paths: instant success, or this puzzle in chunks or all at once.. Approach 1: 1. Create an incoming table with no Identity key 2. Inhale the data. 3. Create a separate DTS task to import the inhaled data to the real table, with Identity key. 4. Package the whole thing into a single DTS task or job, and schedule it accordingly. Approach 2: 1. Commit the Inhale at a specific number of rows, ideally an even division (or mod(1) of the total number of rows) 2. Run what you have and see where it breaks. Could be the last line, could be an errant line somewhere in the middle. At least you inhale all the rows up to the last commit. Approach 3: 1. Much around with the text file looking for oddities that could cause this to happen. My choice is Approach 1, on the grounds that the most important thing is to acquire the data. Importing into a non-Identity table ought to be quick, and also ought to reveal a problem somewhere in the text file. Once you have it in a temp table that SQL considers valid, then it's trivial to append the rows to the actual table. The whole multi-step process can become a single DTS task or job, scheduled if you wish, and buy you time to figure out exactly what's wrong with the input file. Arthur ----- Original Message ---- From: JWColby <jwcolby at colbyconsulting.com> To: dba-sqlserver at databaseadvisors.com Sent: Monday, October 30, 2006 9:24:18 PM Subject: Re: [dba-SQLServer] Appending data to SQL Server table withidentity field 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com