Insert columns into new table where one of the columns doesn’t allow null values

I am attempting to insert 50 rows at a time from one table into another, however I am unable to bypass the ‘Not Null’ column in the table I am attempting to INSERT INTO.

Please note I am not able to alter that column so it accepts nulls.

I have 2 tables (table 1 and table 2). I am inserting 2 columns from table 1 into table 2 (table 2 is currently empty). The problem is that table 2 has a third column which cannot have null values.

This is what I have so far.

-- Checking what exists in Table 1 that doesn't exist in Table 2 before inserting
IF EXISTS (SELECT T1.Column1, T1.Column2
           FROM Table_1 T1
           LEFT JOIN Table_2 T2 on T1.Column1=T2.Column1
           WHERE T2.Column1 IS NULL)
BEGIN
    INSERT INTO Table_2 (Column1, Column2)
        SELECT TOP(50) Column1, Column2
        FROM Table_1
        ORDER BY Column1

    SELECT @@ROWCOUNT
END

IF @@ROWCOUNT < 50
(
    SELECT * 
    FROM Table_2
)

BEGIN
    UPDATE Table_2
    SET Column3 = 0
END

The problem is that Column 3 in Table 2 does not exist in Table 1 so there is nothing I can insert into Table 2 from Table 1 to populate the column that does not allow nulls in Table 2.

Answer

You can select a constant

INSERT INTO Table_2 (Column1, Column2, Column3) 
SELECT top(50) Column1, Column2, 0 
FROM Table_1
ORDER BY Column1

Advertisement

Answer