How to insert millions of records in newly added column to an existing table?

I use alter command to add a new column to an existing Table, successfully it's created with all null values, now I want to insert large data in that column may be millions of records

CodePudding user response：

TableA => Source Table where the newColumn is added ReferenceTableB => Table where the ReferenceColumn which needs to be updated in the newly added column Join => PrimeKEy is the relationship column between the two tables

UPDATE A SET A.NewColumn = B.ReferenceColumn from TableA A inner join ReferenceTableB On A.PrimeKEy = B.PrimeKey

CodePudding user response：

Ideally, what you should have done was told the data engine that you wanted to create the column with a default value. This can be done as part of the ALTER TABLE statement by defining the DEFAULT CONSTRAINT and providing the WITH VALUES clause. I also assume here that you want the column to not be NULLable:

ALTER TABLE dbo.YourTable
ADD NewColumn int NOT NULL CONSTRAINT DF_NewColumn
                           DEFAULT 0 WITH VALUES;

Then if the column should not have a DEFAULT value normally, you can DROP the CONSTRAINT you just created:

ALTER TABLE dbo.YourTable DROP CONSTRAINT DF_NewColumn;

If, however, you didn't do this you'd need to UPDATE the column instead:

UPDATE dbo.YourTable
SET NewColumn = 0;

Using an UPDATE will likely be significantly slower¹ with a large amount of data. As such, if you haven't started using your new column, I would suggest you DROP it, and then recreate it using the first solution (using a DEFAULT CONSTRAINT).

^{1. On my personal rig, the ALTER TABLE when using DEFAULT VALUES took ~2ms for a table with 10,000,000 rows. For the same table, but using an UPDATE is took ~190,000ms (over 3 minutes). For table with 1,000,000 rows the processes were averaging ~1ms and over ~19,000ms respectively.}