Is there a way to auto-assign data types to my table?

Hey guys, quick question...

I just imported a large .csv file, that has about 60 columns, and about 300,000 rows... In order to get the import to work properly I just set all columns to data type string and field size of 500. When I tried to do it the other ways, I just got errors every couple hundred rows about some sort of anomaly...

So now I have this table, is there anyway that I can either copy this table and auto-detect data types or update these data types automatically?


more ▼

asked Jul 04, 2012 at 09:07 PM in Default

avatar image

140 15 15 19

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Usually the setting of the datatypes is done at the time of import. SSIS and other tools, such as Log Parser, can be set to review the first N rows of the sourcefile in order to attribute a datatype to the new (destination) table.

It would appear from what you say that the source data does have anomalies in it if you cannot import the data successfully with more restrictive data type settings.

Is this a characteristic of the data or is it's integrity fundamentally out of whack?

My next step from your position would be to analyse the data, column by column, making data fixes if appropriate or identifying issues as they are encountered. Once you have worked on all the columns you can use TSQL to move the data from your current table to one with a better schema.

You may well be using ISDATE (http://msdn.microsoft.com/en-us/library/ms187347.aspx), ISNUMERIC (http://msdn.microsoft.com/en-us/library/ms186272.aspx) and so on to identify records that fit, or fail to fit, your expectations.

Once you have the datatypes that you want to have applied then create a new table accordingly and then you can use a simple INSERT statement to move the data. You can convert data during this phase or simply refine the data quality in the current table prior to the transfer.

Sadly there is no way to automatically assess the data for the appropriate datatypes where the data is in some way inconsistent as you delve deeper into the recordset.

more ▼

answered Jul 04, 2012 at 09:30 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

One trick I used to use was to import the file into Access first as it is much more forgiving with data types. Then I would import the data from Access into SQL Server which never complained about the Access data even when it would choke on the original file.

I think with SSIS you can redirect the error rows (1 out of 500-ish). This gets the good rows into the table and the "bad" rows into a location where you can look at just them

Jul 05, 2012 at 12:59 AM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 04, 2012 at 09:07 PM

Seen: 1517 times

Last Updated: Jul 05, 2012 at 12:59 AM

Copyright 2018 Redgate Software. Privacy Policy