Inserting Null Values When Using Bulk Insert

I have one table: CREATE TABLE cust ( cust_id NOT NULL, cust_name NOT NULL, address NULL ); I have to insert these rows into another table: CREATE TABLE 1cust_det (

Solution 1:

INSERTINTO
    cust_det
SELECT
    cust_id,
    cust_name,
    COALESCE(address, 'UNKNOWN')
FROM
    cust

Solution 2:

If you have access to change the destination table, just add a default to the column.

CREATETABLE1cust_det (
cust_id NOTNULL,
cust_name NOTNULL,
address NOTNULLDEFAULT'DEFAULT_VALUE');

or if you can edit the existing destination table and it doesnt get drooped

ALTERTABLE1cust_det
ALTER address SETDEFAULT'DEFAULT_VALUE'

The easiest way if you don't have control of the destination table to add a default value to the address column is to use a case statement in the insert itself. In the example below you can also use a ISNULL evaluation, but you might want to search for empty strings as well. Please try to find a better way to insert instead of using a cursor.

INSERT dbo.1cust_det
    (cust_id,cust_name,[address]) 
SELECT cust_id,cust_name,
    CASEWHEN [address] ISNULLTHEN'some default value'ELSE [address]
    ENDAS [address]
FROM cust

Solution 3:

Above answers are correct. You may have another table that may have address for cust_id. Join that table to get missing address. I have seen that in almost all databases, address is stored for every customer. You must get address where address is NULL in the table cust.