Adding new columns to tables and combining the tables

Transact-SQL
1

Hello all

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'
2

You will find life a lot easier if you give constraints a sensible name instead of a system defined name. As the name needs to be unique in the db I tend to do something like:

ALTER TABLE [dbo].[Table1]
ADD [FileName] VARCHAR (50) NOT NULL
CONSTRAINT DF_Table1_FileName DEFAULT 'Table1';
1 Like
3

Thanks Ifor, that does look as if its simpler. But to my question, do i really need to use a constraint at all or do I need to remove the constraint when I try to combine the tables?

4

it depends. if you have the column as NOT NULL without a default constraint then you will always have to provide the value when inserting into the table.

If not required field NULL then you might forget to provide it and it will be NULL

1 Like
5

Unless I'm misunderstanding your requirements, you can accomplish this with a computed column:

No need for a constraint, or any need to persist/materialize/store actual data for the table name.

6

ok I am all set now. All tables have the added column with the file name. I made them all constraint, as such because the column was defaulted with a specific table name, they won't be able to be combined because the constraint defaults are different correct? That's why I was asking do i now have to remove the constraint so that they can be combined into one table

7

What are you actually trying to accomplish here? You started by stating you have 10 tables - all with the same structure and you want to 'combine' them. What is you mean by 'combine'?

If you are trying to replace these 10 tables with a single table - you don't need to do anything at all with the original tables. Just define the final table and insert the data from each table into that final table using a query that specifies the 'new' columns value.

1 Like
8

Jeff
As I was saying, the tables are all set now and they each have a file name column. I was able to insert them into one table by using:

Insert into [MAIN_TABLE]
select * 
from [TABLE_1]

I then do TABLE_2, etc. etc. That worked pretty well until I got to TABLE_8. Now that query runs for over 5 hours when it was only running for an hour while inserting tables 1 through 7. The larger the MAIN_TABLE gets the slower the query runs. (Each table has 75M rows).

9

You still haven't answered the question - what is the purpose of moving this data to a single table? You have already made this process harder than it ever needed to be - if that is the intent.

As to why it is taking so long - I couldn't say without seeing an execution plan, the table definition - and all indexes associated with the table. Since I would have to guess, my guess is the indexes are fragmenting while inserting - causing a lot of page splits to make room on the pages to insert the new data.