SELECT 'UPDATE table SET [' + name + '] = NULL WHERE [' + name + '] = '''';'
FROM syscolumns
WHERE id = object_id('table')
AND isnullable = 1;
Import CSV using “Microsoft Access Database Engine 2010” to SQL Server
sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure with override SET NOCOUNT ON EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo [Test.txt] > C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo ColNameHeader=True >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Format=Delimited(^|) >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo MaxScanRows=50 >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo CharacterSet=OEM >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col1=ID TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col2=LastName TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col3=FirstName TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col4=MiddleName TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col5=MailingAddressLine1 TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col6=MailingAddressline2 TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col7=MailingAddressCity TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col8=MailingAddressState TEXT >> C:\Temp\Schema.ini' EXEC master..xp_cmdshell 'echo Col9=MailingAddressZIP TEXT >> C:\Temp\Schema.ini' EXEC ('INSERT INTO ImportTest (ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP) SELECT ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=C:\Temp\;HDR=YES'',''select * from person.txt'') ') EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'