I need assistance pulling a big db into SQL Server

[MadPiet]

Where's the file? I wanna see if I can get it to insert.

 

[jwcolby54]

What is this? And how do I use it?

 

[jwcolby54]

I have a text file. It appears to be a csv file.

 

[jwcolby54]

I have an xml file

 

[jwcolby54]

I have a schema.ini

 

[jwcolby54]

What I don't have is it imported into SQ Server!

To use BCP I had to go into the ini and manually copy the ini field lengths into the BCP field mapping thingie. Thingie is a very scientific term I picked up somewhere.

 

[jwcolby54]

Having done that I successfully pulled in several million records out of (apparently) somewhere around 60 million addresses. I got some dat from around 20 states, many just a few thousand records. Some states with tens or hundreds of thousands of records. My guess is that those states were simply first in the file position wise.

 

[jwcolby54]

BCP was unable to handle the very common field data like

Peoria,,,"Country Meadows Units 5, 6, & 7",,,,,,AZ,

With field delimiters of "" and inside that comma delimited strings. Failed to import every record with such data and there were a ton of them. So BCP kinda worked but not well...

 

[jwcolby54]

Where's the file? I wanna see if I can get it to insert.

https://www.transportation.gov/gis/national-address-database

 

[jwcolby54]

https://www.transportation.gov/gis/national-address-database

It took me hours to download, although my internet down speed isn't particularly speedy.

 

[amorosik]

IIRC it is a 36 gb file. Compressed. However I might have it in a usable format already. Do you know what GDB files are?

Interbase / Firebird
If the file format was really Interbase/Firebird then you could use this db server directly and connect via odbc to the data
No conversion, no data loss, no license fee to pay, and an exceptional db server to use for every purpose

 

[jwcolby54]

Where's the file? I wanna see if I can get it to insert.

The file is a 36gb download for the text download. I can't just append it to a message here.

And that is zipped.

 

[jwcolby54]

Interbase / Firebird
If the file format was really Interbase/Firebird then you could use this db server directly and connect via odbc to the data
No conversion, no data loss, no license fee to pay, and an exceptional db server to use for every purpose

I think this is a geo database, not the one I was trying to import.

 

[Isaac]

Well this has been a project. I am looking for a reasonably large test data table, something I could use to create a demo for dependent objects filtering, using a combo for filtering some table, then using that combo to filter some other table etc. At least two but preferably several levels deep. So says I, a large address database could allow me to treat a single table as if it were several. As an example, I could setup a listcontrol to filter for one or several states. Once I select those states I could set up the next to filter for last names. How many Colbys live in Texas. In California? In Arizona and New Mexico.and Utah. OK now what first names do those Colbys have.

Something like that. I really did not want to spend a bunch of time building a bunch of tables, clients, orders, line items or some such.

Soooo... I found this thing called the National Address Database. Free, maintained by the government, literally something like 60 million addresses. Cool, says I, let's grab that. Well yea! It requires SQL Server of course. And it don't fit in SQL Server Express. So I had to download the dev version, which I did.

So one would think a bulk copy import would be the ticket. One would be wrong!!! First off it is a csv, comma delimited. Whoever created thing thing created just a ton of fields where the field contained data like this "Something, Something Else, Something other". Bulk copy can't deal with the quotes with comas inside. The comma is the field delimiter and so it tries to break that string out into data for three fields.

I did get manage to get 4 million records in just using the BCP and ignoring errors but it died well before the end with a million errors. Even that I had to do 10,000 record updates all along the way. And holy cow, it was dog slow. I am doing this on my 8 core 16 thread 64gb ram huge ssd laptop. Plenty of horsepower.

So being the perfectionist that I am...

So I am trying to use SSIS ( I think it is called) which can deal with the quotes and embedded commas to correctly place that data into a single field.

You can see where I am going with this. What should be simple ain't. I got ssis to show me the thing, accept the first line as field info. etc but now it wants to use a standard 50 character string per field ans some fields have 255 characters.. There is an ini with field defs but it can't just use an ini.

So... I have spent a bunch of time trying to import a large table into SQL Server. SQL Server is a powerful beast but my forte is Access and event programming in vba.

SSIS can import anything - and you can change the length of the columns in the configuration.

Also have you tried using the SQL Server Import/Export Wizard?

 

[MadPiet]

no need for the Import Export wizard. I'd use BULK INSERT for that. It's crazy fast. SSIS in this case is overkill I think. If you create a format file, you're sorted. I imported something like a 10 GB file in less than a minute. (I'd skip the indexing until after the table is populated.)

 

[jwcolby54]

no need for the Import Export wizard. I'd use BULK INSERT for that. It's crazy fast. SSIS in this case is overkill I think. If you create a format file, you're sorted. I imported something like a 10 GB file in less than a minute. (I'd skip the indexing until after the table is populated.)

Bulk import failed because This was a "csv" file. and there were many records with various fields with data enclosed in quotes "" containing commas.

Some Field Data: "Some Piece, Some other piece, Some third piece" all in a single field in the table to be imported. CSV import expects the comma to denote the next field. Bulk import could not handle this. Or if it could I could not discover how.