-1

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 (
    cust_id NOT NULL,
    cust_name NOT NULL,
    address NOT NULL
);

Unfortunately the cust.address column contains NULL values. I want to insert these rows from cust to 1cust_det using a cursor. What do I have to do?

Bryan
  • 17,112
  • 7
  • 57
  • 80
user3768640
  • 1

3 Answers3

2
INSERT INTO
    cust_det
SELECT
    cust_id,
    cust_name,
    COALESCE(address, 'UNKNOWN')
FROM
    cust
MatBailie
  • 83,401
  • 18
  • 103
  • 137
1

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

CREATE TABLE 1cust_det (
cust_id NOT NULL,
cust_name NOT NULL,
address NOT NULL DEFAULT 'DEFAULT_VALUE');

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

ALTER TABLE 1cust_det
ALTER address SET DEFAULT '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,
    CASE 
        WHEN [address] IS NULL THEN 'some default value'
        ELSE [address]
    END AS [address]
FROM cust
John Turner
  • 45
  • 5
  • Note; default values are only applied when the insert statement does not specify the column. Thus meaning that either All the rows inserted get the default value, or you bump I to the same problem. – MatBailie Jun 23 '14 at 19:47
0

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.

Vishnu Gupta
  • 19
  • 2