[dba-SQLServer] table definition file

jwcolby jwcolby at colbyconsulting.com
Sat Mar 24 08:07:39 CDT 2012


Thanks for the offer Stuart.

It turns out that the file was comma delimited CSV with the first line being field defs.  I just 
imported the file using SQl Server's import wizard.  Then it turns out that the table definition 
file didn't even remotely match the actual fields in the data file.  *CUTE*!  If I had spent a bunch 
of time on pre-processing I'd have been annoyed.

The SQL Server import widget is happily importing the file as we speak.  179 million records so far 
after about 18 hours of importing.

At this point my biggest problem is that the first line in the file, the field def line, also had 
quotes around them (comma delimited) and for some reason the wizard removes the quotes in the data 
but not the field names.  Thus I have a table with leading and trailing quotes in every field name 
which I will have to figure out how to strip out.  IIRC I cannot rename fields in SQL Server if 
there is very much data in the table, and there is just a bit of data in this table - 182 gigs so far.

I do have one question.  The import wizard uses varchar(50) as the default field size.  I had to 
modify one field (the email field) to longer than that, however many of these fields are just a 
single character.  Is there metadata which SQL Server stores to tell me what the longest actual data 
in each field is?  And if so, is there any advantage to "shrinking" the field size from varchar(50) 
to char(1) for the hundreds of single character "code" fields or the other fields where the actual 
data is varchar but shorter - 15 characters or 40 characters?

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 3/23/2012 4:54 PM, Stuart McLachlan wrote:
> I'd  just use a text editor with decent regexp find/replace (Crimson Editor?) to convert the list
> into a Create Table SQL query.  It should only take a couple of minutes.
>
> Heck, if you want to send me the definition file, I will do a first pass on it for you.
>



More information about the dba-SQLServer mailing list