Simplified example of what I have:

Two tables (table\_1 and table\_2) which have one similar column (Id) but also several "payload" columns with different names (col\_1\_1, col\_2\_1, col\_2\_2). Amount of "payload" columns is different for different tables.

I am interested in extracting the IDs from both tables into another table for rows which have all "payload" columns empty.

There is a list of all "payload" columns for all tables which can be used (#temp)

This is how it is done with cursor:

```
CREATE TABLE #temp (tab nvarchar(20) not null, col nvarchar(20) not null)
INSERT INTO #temp SELECT 'table_1','col_1_1' UNION SELECT 'table_2','col_2_1' UNION SELECT 'table_2','col_2_2'

DECLARE @table_name nvarchar(20)
DECLARE @sql nvarchar(max)

DECLARE curs CURSOR FOR (SELECT DISTINCT tab FROM #temp)
OPEN curs
FETCH NEXT FROM curs INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = ISNULL(@sql,'')+col+' IS NULL AND ' FROM #temp WHERE tab = @table_name
    SET @sql += 'Id IS NOT NULL'
    SET @sql = 'INSERT INTO #temp_master SELECT ID FROM '+@table_name+' WHERE '+@sql
    print @sql
    SET @sql = ''
    FETCH NEXT FROM curs INTO @table_name
END
CLOSE curs
DEALLOCATE curs

```

This is the result:

```
INSERT INTO #temp_master SELECT ID FROM table_1 WHERE col_1_1 IS NULL AND Id IS NOT NULL
INSERT INTO #temp_master SELECT ID FROM table_2 WHERE col_2_1 IS NULL AND col_2_2 IS NULL AND Id IS NOT NULL

```

Is it possible to remove the cursor to get the same resulting dynamic query? The problem is that I am unable to have dynamic "IS NULL AND" part for different tables when I remove the cursor.