Can anyone explain this error and maybe even suggest how to avoid it?

I came across a very strange and unexpected error today, and while I managed to make it "go away" I cannot for the life of me understand why it happened in the first place. And I would like to invite a discussion on what could have caused it, whether it's possible to recognise the situation if it occurs again, and how to prevent it happening in future.

I cannot use proprietary data so I can only describe the problem in generic terms

I am transferring data between two Azure databases. To do this, I use an external data source. The "from" and "to" tables have the exact same layout, so:

Step 1, I create a "work" table by doing a SELECT INTO

SELECT TOP 0 [Column1], [Column2], [Column3] INTO [KII].[KIIWorkTable] FROM [dbo].[maintable]

Step 2, I add the [$ShardName] column

ALTER TABLE [KII].[KIIWorkTable] ADD [$ShardName] NVARCHAR(128) NOT NULL

Step 3, read the data from the external data source which also has the maintable with an identical definition

INSERT
  INTO [KII].[KIIWorkTable]
EXEC sp_execute_remote [externaldatasource], N'SELECT [Column1], [Column2], [Column3] FROM [dbo].[maintable]

Step 4, remove the shard name. I don't need it anymore

ALTER TABLE [KII].[KIIWorkTable] DROP COLUMN [$ShardName]

So far everything worked perfectly, but now something really bizarre happens

Step 5, insert the additional rows:
INSERT
  INTO [dbo].[maintable]
      ([Column1],
       [Column2],
       [Column3])
SELECT [KID].[Column1],
             [KID].[Column2],
             [KID].[Column3]
  FROM [KII].[KIIWorkTable] [KID]

And SQL complains that the columns in the insert do not match the columns in the select. Of course in my real world scenario the tables have a lot more columns and the situation is much more complex, but I eventually narrow it down by starting with just one column and slowly trying it again and again, adding more columns as I go on

INSERT
  INTO [dbo].[maintable]
         ([Column1])
SELECT [KID].[Column1] .....

Works perfectly

I keep going until I hit [Column2] and the problem resurfaces

I skip [Column2]

INSERT
  INTO [dbo].[maintable]
         ([Column1],
          [Column3])
SELECT [KID].[Column1],
             [KID].[Column3] ......

It works perfectly. It doesn't work if I keep [Column2] between the first and last column, but

INSERT
  INTO [dbo].[maintable]
         ([Column1],
          [Column3],
          [Column2])
SELECT [KID].[Column1],
             [KID].[Column3],
             [KID].[Column2] ......

What the..... NOW it works!?!!

So obviously there is something not quite right with [Column2]. I look at the table and I see [Column2] is nullable and it contains NULLs in some rows. And I happen to know that it's an integer, and the database design is not ideal, but for our business logic NULL and 0 are interchangeable so I run an update

UPDATE [dbo].[maintable] SET [Column2] = 0 WHERE [Column2] IS NULL

And now the original statement works as expected. While doing this fixed the problem, I know NULL values aren't the problem. I have done the same thing for several other tables and even this table has other columns that are nullable and that contain nulls, and the same thing doesn't happen for those. I can keep the columns in the order they are defined in the DB schema and the inserts just work, nulls or no nulls.

So I'm just wondering what the underlying reason for this could be. Is there anything I can run in t-sql to recognise the scenario if it occurs so I can fix it BEFORE it causes an issue?