asked on

How to convert a table with VARCHAR(MAX) fields to more appropriate data types in SQL

Hi
I am trying to load a csv file into a SQL database. The csv file has been created from another database using the Save Results As csv option, and therefore the string "NULL" appears in many columns of the csv file which correspond to fields that should not contain strings, eg int or datetime fields. Although SSIS would seem to be the right tool for helping me import the csv data into a new SQL table, the "NULL" issue (amongst others) has caused me so many problems that I have given up on using SSIS.

I am therefore now trying an alternative two-step approach, which is to:

1. Use BULK INSERT to load all the data into a SQL table that is constructed using VARCHAR(MAX) as the data type for all fields. This has been achieved.

2. Use a method to convert the contents of the VARCHAR table to a second table that contains the same field names, but with the fields having the correct data type, Ocurrences of "NULL" should be entered as NULL in the destination data fields. The question is, how should I achieve this second step?

Thanks

Christopher Gordon

Can you use the NULLIF() function? When loading from staging table to destination table, just wrap the source table fields in this statement:

select nullif([StagingTable].[FieldA],'NULL') as FieldA

I

qprjohn121

ASKER

Gohord - Sounds v promising. At a more basic level, how would I perform the load from staging table to destination table - do you mean to use the NULLIF function as part of a SELECT INTO statement? Or as part of another construct?

thanks

ASKER CERTIFIED SOLUTION

Christopher Gordon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

qprjohn121

ASKER

Thanks, you have understood my question perfectly. I've got to shut down for the moment so will try your solution tomorrow. Very hopeful that this will work.

Scott Pletcher

You should also consider putting some mechanism into place if possible to automatically prevent you from accidentally loading the same staged data twice.

qprjohn121

ASKER

Worked for me perfectly thanks. NULLIF is my new favourite function!