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

qprjohn121
qprjohn121 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Christopher GordonSenior Developer Analyst

Commented:
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

Author

Commented:
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
Senior Developer Analyst
Commented:
I'm assuming your staging table is being populated by the Bulk Insert.   At that point, your staging table is filled with text value "NULL".  You don't want "NULL" to be pushed to the destination table.  So you can do something like this

Insert into DestinationTable
(
 fieldA,
FieldB,
...
)

select
nullif([fieldA],'NULL'),
nullif([fieldB],'NULL'),
....
from stagingTable

Apologies in advance if I misunderstood question. :)
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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 PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial