Finding and Copying Null Values from One Table to Another in SQL Server

As a database professional, you have encountered situations where you need to find all null values from respective columns of a table and then copy or insert those null values to respective columns of another table that has an exact schema like the original table. In this article, we will explore how to achieve this task efficiently using SQL Server.

Understanding the Problem

Suppose we have two tables, table_A and table_B, with an identical structure but in a different database schema (YourSchemaName). We want to copy null values from table_A to table_B. To do this, we need to find all columns where there are null values in table_A and then insert those same null values into the corresponding columns of table_B.

Finding Column Names of a Table

The first step is to find the column names of table_A. We can use the INFORMATION_SCHEMA.COLUMNS system view, which provides information about tables, columns, and their properties.

To retrieve only the column names, we can use the following query:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_A' AND TABLE_SCHEMA='YourSchemaName';

This will return a list of column names in table_A.

Creating a Cursor for Results

To loop over the column names, we need to create a cursor that fetches each column name one by one. This can be done using a DECLARE, OPEN, and WHILE loop structure.

Here’s an example code snippet:

DECLARE column_cursor CURSOR FOR 
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'table_A' AND TABLE_SCHEMA='YourSchemaName';

OPEN column_cursor;

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM column_cursor INTO @column_name;
END;

CLOSE column_cursor;
DEALLOCATE column_cursor;

In this code, we declare a cursor named column_cursor that fetches the column names from table_A. We then open the cursor and enter a loop where we fetch each column name using the FETCH NEXT statement.

Building the WHERE Clause

Inside the loop, we need to build a WHERE clause that includes all the column names. If the column is null, we add it to the WHERE clause using the IS NULL condition. We can use the following logic:

IF ISNULL(@where)
BEGIN
    @where = CONCAT(' ', @column_name, ' IS NULL ');
END
ELSE
BEGIN
    @where = CONCAT(@where, ' OR ', @column_name, ' IS NULL ');
END;

This code initializes the @where variable to null and then checks if it is not empty. If it’s not empty, it concatenates the current column name with the IS NULL condition.

Building the UPDATE Statement

Next, we need to build an UPDATE statement that inserts the null values into the corresponding columns of table_B. We can use the following logic:

IF ISNULL(@set)
BEGIN
    @set = CONCAT(' ', @column_name, ' = CASE WHEN source.', @column_name, ' IS NULL THEN NULL ELSE target.', @column_name, ' END ');
END
ELSE
BEGIN
    @set = CONCAT(@set, ', ', @column_name, ' = CASE WHEN source.', @column_name, ' IS NULL THEN NULL ELSE target.', @column_name, ' END ');
END;

This code initializes the @set variable to null and then checks if it is not empty. If it’s not empty, it concatenates the current column name with the SET clause using a CASE statement.

Building the Final UPDATE Query

Now that we have built the WHERE and SET clauses, we can combine them into a single UPDATE query:

@query = CONCAT('UPDATE target SET ', @set, ' FROM source JOIN target ON source.id = target.id WHERE ', @where, ';');

This code concatenates the SET clause with the FROM and WHERE clauses to form the final UPDATE query.

Executing the UPDATE Query

Finally, we can execute the UPDATE query using the EXEC statement:

EXEC (@query);

This will update table_B with the null values from table_A.

Conclusion

In this article, we explored how to copy null values from one table to another in SQL Server. We discussed how to find column names of a table using the INFORMATION_SCHEMA.COLUMNS system view and how to create a cursor that fetches each column name one by one.

We then built a WHERE clause and an UPDATE statement using these column names, which can be used to copy null values from table_A to table_B. Finally, we combined these components into a single UPDATE query that can be executed using the EXEC statement.

I hope this article has been helpful in teaching you how to achieve this task efficiently using SQL Server.

Last modified on 2023-12-20