Jim Lawrence
accessd at shaw.ca
Tue May 1 16:43:20 CDT 2007
Hi John: SQL Server 2005 was created as a database for manipulating data within it. It was not designed as a application for importing millions of rows on a steady bases. You are going to have to become a master of the bulk-loader or be patient with limited speed of a pre-processor. It might be worth investing in a good data conversion tool even though developers are pre-conditions into: rolling-your-own. For example here is a tool that might be worth looking into: http://www.utexas.edu/its/rc/tutorials/stat/spss/spss1/ or even Microsoft's BixTalk Server (there is a 120 day evaluation full copy at http://www.microsoft.com/technet/prodtechnol/biztalk/2006/downloads/default. mspx) which is designed for doing tricky conversion routine. You can add whole program to manage the importation of a single field.... and that sounds like what you need. HTH Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Tuesday, May 01, 2007 10:51 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Bulk insert ROTFL. One problem I have is a "big boy" database that requires a toy to feed it data. And yes, that is the fault of the tool. Luckily I am an expert at programming the "toy" so I can get around the shortcomings of the ... Hmm... "big boy's database". I do notice that after all my posts there is dead silence about getting SQL Server to do what I need in an expeditious manner. Having combed the web I also find that there are hundreds of posts about this shortcoming of SQL Server. "Oh, well... You can import to a temp table and then use xxx to strip the spaces and then use YYY to get the data where it really should have been put in the first damned place by this "big boy's database". Yea, except that I have 60 of these files to do, and another 100 coming in a few weeks and another 50 shortly after that. It is waaaay faster to program my "toy" to feed it data. I will be the first to say that SQL Server is a powerful database engine, that is why I use it. I am also however willing to admit that it sucks in some very visible ways. Learning curve my hind ass. 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 David Lewis Sent: Tuesday, May 01, 2007 1:29 PM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Bulk insert John: There are a few websites concerned with sql server, with active forums. I recommend you check them out. One is sqlcentral.com, the other is sswug. You are right, access is a 'toy' when compared to sql server. One problem you are having is that you don't have the luxury of a learning curve -- but that is not the fault of the tool. Hang in there. D And that is exactly what I am doing and it is soooooo KLUDGY!!! I am running a program I wrote to read the data out of the original fixed width file because SQL Server won't handle fixed width files and strip off the spaces. How incredibly stupid is THAT? Is there ANYONE out there who WANTS those spaces? So I am already using KLUDGE to get data into SQL Server. Now I export it out to a perfectly valid CSV file only to discover that SQL Server BCP and Bulk Insert don't even look at (understand) quotes around comma delimited fields. But ACCESS does. But Access is a TOY remember? What exactly does that make SQL Server that it needs a toy to feed it data? This has been an exercise in discovering just how brain dead the data import processes are (or can be anyway) for SQL Server. This is NOT rocket science. I am able to write a utility to open / import / mangle / export it back out to another file in VBA. How tough can it be to do this import inside of SQL Server natively? I have no idea how widespread this kind of file is but I can tell you that that is all I see EVER in the industry I am dealing with. HUGE files, fixed width, space padded right. And I can tell you they have been a royal PITA to get into SQL Server. At least now I have my own utility that can get I these input files into the format I need, even if it is in ACCESS/ VBA. My next step is to port this to VB.Net so that I can do it a little more "natively". Once I get a little more familiar with VB.Net I want to look at storing the data right into a recordset in ADO and then write that back to SQL Server. If that is too slow (I suspect that it will be) then I can still do what I do now and import / mangle / write to file and then run a stored procedure to do a Bulk Insert from the file I create. 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