Eric Barro
ebarro at verizon.net
Mon Apr 30 18:31:07 CDT 2007
Have you looked into BULK INSERT in SQL? This is supposed to be a faster data import method. Using T-SQL you can do something like this... CREATE TABLE #tmpEmployees (<fieldnames here>) BULK INSERT #tmpEmployees FROM 'c:\temp\import.csv' WITH (FORMATFILE = 'c:\temp\importCSV.fmt' importCSV.fmt would contain the file format...in this example it's fixed width 8.0 18 1 SQLCHAR 0 5 "" 1 suffix SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 30 "" 2 last_name SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 20 "" 3 first_name SQL_Latin1_General_CP1_CI_AS -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Tuesday, April 24, 2007 9:07 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] using a saved SSIS with VB.Net The CSV file is on the same machine. It appears that the clause that pulls the source table (csv file) into memory is taking a ton of time. These are large files, the smallest are a little under 200 million bytes and the largest are up in the 3 gigabyte range. It appears that SQL Server does not read a few records and append them, but rather reads the whole CSV and then starts appending all of the assembled records. If I were a SQL Server pro I could probably speed this up considerably. Alas, I am not. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart Sent: Tuesday, April 24, 2007 11:53 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] using a saved SSIS with VB.Net John, One of the keys to getting it into SQL Server faster is to have the CSV file on the server and not on a different machine. Network traffic can kill the process and slow it down significantly. Robert At 10:22 AM 4/24/2007, you wrote: >Date: Tue, 24 Apr 2007 10:32:44 -0400 >From: "JWColby" <jwcolby at colbyconsulting.com> >Subject: Re: [AccessD] using a saved SSIS with VB.Net >To: "'Access Developers discussion and problem solving'" > <accessd at databaseadvisors.com> >Message-ID: <003101c7867d$6cce93a0$657aa8c0 at m6805> >Content-Type: text/plain; charset="us-ascii" > >Gustav, > >My bigger issue here is that there 56 of these files to import into SQL >Server, supposedly ~100 million records. I have done about 8 million >records so far. I really must get this thing automated such that it >just chunks through these CSV files without my having to be around to >start the next one. I am working now on setting up the append query >using that syntax below into a stored procedure so that I can then just replace the file name. >After that I will need to write something in VB.Net or whatever to >execute the stored procedure feeding in all of the file names from a >specific directory, deleting the file once the stored procedure >finishes the import for a given file. > >I have never written a stored procedure. >Obviously, given the above, I have never called a stored procedure from >code. > >So much to learn, so little time. > >Once this is imported I have to turn right around and export a subset >of fields from the table back out as 1 - 2 million record chunks for >CAS / DPV / NCOA processing, then I have to import THOSE back in to a >new table. > >And when this set of data is finished, I have another set of about the >same size on the way, to which I have to perform the same processes. I >soooooo need to get this process automated. > >John W. Colby -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.463 / Virus Database: 269.5.10/774 - Release Date: 4/23/2007 5:26 PM