Illogical script generated when inserting nullable column
bmatson
Posts: 4
I reported this defect against the version 5 series for SQL Compare, however it still seems to exist in version 6.....so watch your back!
I have multiple, re-createable examples where, when "Re-building" a table due to the insertion of a nullable column in the middle of the table the script does something which makes no sense to me. In the insert statement to the temp table (which gets renamed back to the original table name) instead of inserting NULL values for the new column the script fills in the new field with the values of the NEXT column rather than a NULL. I can't concieve of where this would be a desirable behavior. See the following from the script, the [Type75] column is a new, nullable column which has been inserted into the table. This is the snippet of SQL from SQL Compare which purports to "Re-build" the table while "Preseving" the data:
INSERT INTO [dbo].[tmp_rg_xx_TableName](
[PropertyID],
[Active],
[Type30],
[Type60],
[Type75],
[Type90],
[NonRenewalType])
SELECT [PropertyID],
[Active],
[Type30],
[Type60],
[Type90],
[Type90],
[NonRenewalType]
FROM [dbo].[TableName]
Bill
I have multiple, re-createable examples where, when "Re-building" a table due to the insertion of a nullable column in the middle of the table the script does something which makes no sense to me. In the insert statement to the temp table (which gets renamed back to the original table name) instead of inserting NULL values for the new column the script fills in the new field with the values of the NEXT column rather than a NULL. I can't concieve of where this would be a desirable behavior. See the following from the script, the [Type75] column is a new, nullable column which has been inserted into the table. This is the snippet of SQL from SQL Compare which purports to "Re-build" the table while "Preseving" the data:
INSERT INTO [dbo].[tmp_rg_xx_TableName](
[PropertyID],
[Active],
[Type30],
[Type60],
[Type75],
[Type90],
[NonRenewalType])
SELECT [PropertyID],
[Active],
[Type30],
[Type60],
[Type90],
[Type90],
[NonRenewalType]
FROM [dbo].[TableName]
Bill
Comments
I had a look through our bug database and I couldn't find any examples for this problem. If you could send me your examples (michelle.taylor@red-gate.com) then I'll take a look at the issue.
(I tried a simple case - adding a nullable column in the middle of a table and reordering the columns to force a rebuild - but that just inserted the old values into the old columns and left the new column alone for SQL Server to automatically fill with NULLs. Just adding a nullable column didn't force a rebuild in my simple example.)
Thanks for your report,
Michelle Taylor
Redgate Software
Eddie Davis
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com