I have 10 tables all with the same columns that I want to combine while at the same time adding a new column so I can identify which table the data came from. I used the below code to add the new column to the first table:
ALTER TABLE dbo.[Table1] ADD FileName VARCHAR (50) NOT NULL Constraint DF_FileName default 'Table1'
I tried to do the same with the others but had to change the constraint to DG, DH, etc. When I attempted to combine the tables, the table was a mess. More rows than there should have been and the new column did not get added. I suspect that is due to the constraint on the newly added column. I did research and learned that I can remove the constraint and then there shouldn't be a problem when combining the tables but before doing so I wanted to confirm that that is correct.
Also, is it necessary to add the constraint at all? Could I not just run this query to add the new column?
ALTER TABLE dbo.[Table1] ADD FileName VARCHAR (50) NOT NULL Default 'Table1'