Put the data into a staging table and then insert to the production table using SQL code.

update table1
set field1 = NULL
where field1 = 'null'

Or if you want to do for a lot of fields

update table1
    set field1 = case when field1 = 'null' then Null else Field1 End
      , field2 = case when field2 = 'null' then Null else Field2 End
      , field3 = case when field3 = 'null' then Null else Field3 End

How to import blanks as nulls instead of zeros while importing txt , StringField,IntField a,1 b,5 c, d,6 e, f,8 g, h, i,10 j, When you import the data using the wizard the nulls are converted to 0's It generates an SSIS package for you and chooses some defaults, unfortunately not the defaults  For bulk import in SQL Server, both bcp and BULK INSERT load default values to replace null values. For both, you can choose to retain null values. Keep nulls or default values during bulk import - SQL Server | Microsoft Docs

KISS

Pre-process it, Replace all "NULL" with "".

ie the .csv file will have

,,

Instead of

NULL,NULL,

Seems to do the job for me.

SSIS: How to import null string as null mark in flat files, Scenario: I have some requirements: 1. Save the SQL result as CSV file 2. Import the data from the CSV file into another SQL database. When I attempt to import a .csv comma-delimited flat file into a Microsoft SQL server 2008R2 64-bit instance, for string columns a NULL in the original data becomes a literal string "NULL" and in a

Adding to HLGEM's answer, I do it dynamically, I load into staging table here all column types are VARCHAR and then do:

DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql = CONCAT(@sql, '
    UPDATE [staging].[',[TABLE_NAME],']
    SET [',[COLUMN_NAME],'] = NULL
    WHERE [',[COLUMN_NAME],'] = ''NULL'';
    ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_SCHEMA] = 'staging' 
    AND [TABLE_NAME] IN ('MyTableName');
SELECT @sql;
EXEC(@sql);

Then do:

INSERT INTO [dbo].[MyTableName] ([col1], [col2], [colN])
SELECT [col1], [col2], [colN]
FROM [staging].[MyTableName]

Where table [dbo].[MyTableName] is defined with the desired column types, this also fails and tells you in type conversion errors...

Keep nulls or default values during bulk import, For bulk import in SQL Server, both bcp and BULK INSERT load default values to replace null values. For both, you can choose to retain null  Open SQL Server Management Studio. Connect to an instance of the SQL Server Database Engine or localhost. Expand Databases, right-click a database (test in the example below), point to Tasks, and click Import Flat File above Import Data. We are listening: If you find something outdated or incorrect in this article,

3. Data, Tables, and Database Design, But when we try to sort those temperatures from high to low, Access treats negative click the Tables/Queries tab, and look in the SQL Server Compatible Syntax area to Create a blank query and paste this code into SQL View; then run it. The Import Wizard (File → Get External Data → Import) runs fine, but then it gives  I am using SQL Server 2008 Import Export wizard to bulk import a text file. The text file contains more than 9 Lakh records with column delimiter | and row delimiter / terminator as {LF} Everythi

How to Get SQL Server Data-Conversion Horribly Wrong, That said, it's not enough to simply convert numeric data to a string. Unless you pass in a literal NULL as the first expression, ISNULL You should also know how your expressions, functions, and other elements treat data when it is When you import Excel data into SQL Server, the OLE DB provider  SSIS doesn't implicitly convert data types, so you need to do it explicitly. The Excel connection manager can only handle a few data types and it tries to make a best guess based on the first few rows of the file. This is fully documented in the SSIS documentation. You have several options: Change your destination data type to float.

[PDF] MySQL Workbench, 6.5.2 SQL Data Export and Import Wizard . MySQL Workbench is a graphical tool for working with MySQL servers and Right-clicking a blank area in the MySQL Connections area now offers an Whether to treat identifiers separately if their enabled, you cannot use double quotation marks to quote literal strings,. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse. A constant, also known as a literal or a scalar value, is a symbol that represents a specific data value. The format of a constant depends on the data type of the value it represents. Character string constants are enclosed in single quotation

Comments

• This is a horrible, horrible solution. "NULL" is a perfectly valid string and not the same as null, so don't treat it as such. Things like this are exactly what lead to troubles for all the poor people with a "Null" lastname.
• @Voo, Did you read the question? Null was not a valid value for the OP.
• @HLGEM The OP is also talking about string columns and nowhere says anything whether "NULL" is valid or not - just that "NULL" will throw an error in integer columns.