Can you tell me how to avoid below mentioned exception ?
INSERT INTO [Migrated].[dbo].[Cities] (Name, ZipCode)
[Legacy].[dbo].[Ziplist] AS a
(a.City IS NOT NULL AND a.ZipCode IS NOT NULL);
Cannot insert the value NULL into column 'IsDeleted', table 'Migrated.dbo.Cities'; column does not allow nulls. INSERT fails.
As @jamieD77 commented you are missing the
IsDeleted column in your insert statement.
The error means that the column is marked as "NOT NULL" and therefore a value must be inserted when a new row is created.
So you either need to remove the NULL constraint from the table yourself or insert a value in the column.
INSERT INTO [Migrated].[dbo].[Cities] (Name, ZipCode, IsDeleted) select DISTINCT a.City,a.ZipCode,0 from [Legacy].[dbo].[Ziplist] as a where ( a.City is not null and a.ZipCode is not null);
binary field which I would assume it is (but you should confirm!) the value
0 would be
1 would be
true. If the field is a different data type these values may have different meanings!!!