I need assistance pulling a big db into SQL Server

[jwcolby54]

I downloaded the National Address Database last night. Unzipped it is 33 gb and change which is a tad too big for MS Access <grin> or even SQL Server Light. They provide a schema.ini with the field names and widths which seems like it could be used directly in the import process. But it has been to long since I did a bulk copy input and I thought I'd ask for assistance before I began the struggle.

I own SQL Server but my big databases are on a desktop machine which I don't use any more. I am working on an 8 core amd laptop with 64gb SSD, with plenty of room on an ssd. This is my dev machine now and I would like to get this address database loaded into sql server on this laptop. It is more than I actually want but it is free and it has a TON of data for testing purposes.

BTW the last time I did this the fileds were fixed width with space padding to the right. I have no idea whether that will be the case here but if so I need to get rid of the padding as well. AFAICT this is a single big table.

Can anyone help me with this. Thanks

 

[jwcolby54]

Well maybe no help needed. The import wizard is working. Thank goodness for the ini file. I had to manually modify all the lengths to match the ini. Then I had to find and fix "does not allow null" errors for a handful of fields. The import is now running. It will take awhile I suspect.

 

[Minty]

Possibly a daft question, but what format is the data in that you have downloaded?

 

[jwcolby54]

Possibly a daft question, but what format is the data in that you have downloaded?

It claims to be a csv although the file extension is txt. It has a header row, and after that the fields are comma delimited, so likely a csv. It was running but now got a timeout error. This is a big file, which means a big import process. How do I modify the timeout period? I am working with chatgpt but a real human is always (usually) better

It took awhile to modify all the field widths. Is there a way to save this data from the wizard if I have to leave the wizard to do something?

BTW I just matched the .ini file for the field widths. That seems to be working. It is finding fields that require 'allow nulls" which the wizard did not pick up by itself. Probably a half dozen or more.

 

[jwcolby54]

It seems that I was working with SQL Server light and ran into the max size limit. I am downloading and installing the developer version. I'll see if I can get it done there.

 

[jwcolby54]

I'm here to say that ChatGPT is an awesome tool. It worked with me to get the disk files moved out to a larger disk.

On another front... I have a registration problem with Word trying to hook the preview in explorer and failing with an obnoxious popup that cannot be gotten rid of except with task manager. ChatGPT walked me through to the point that I now understand that much. I can preview many things, but .odt files... Word kicks in as the preview handler (and fails) and what a PITA that is.

We have also looked at a bunch of stuff including deep into the registry where sure enough Word is doing the preview for .odt files. It hooked me up with AutoTRuns and how to search through stuff in there (nothing found there). I have to say I am amazed the depth of knowledge.