0

I have a table with 30k rows. Adding a column (e.g. below) takes ~10 seconds.

I need to add 50-odd columns, but this takes 10 minutes.

ALTER TABLE myTable ADD COLUMN IF NOT EXISTS myCol1 DOUBLE;
ALTER TABLE myTable ADD COLUMN IF NOT EXISTS myCol2 VARCHAR;

I've seen this syntax:

ALTER TABLE myTable ADD (myCol1 DOUBLE, myCol2 VARCHAR);

Which seems faster (or at least, a constant time rather than a multiple off the number of columns) but this doesn't seem to support IF NOT EXISTS.

Is there any way to optimise this?

Jakg
  • 922
  • 12
  • 39
  • assuming that this addition is a one-time thing, why wouldn't you create a back up of a table; drop it; create new with all necessary columns; repopulate from a back up. Hope this helps. – user2065377 Feb 13 '19 at 19:23
  • also you can see here how to add multiple columns in one statement: https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server?rq=1 – user2065377 Feb 13 '19 at 19:33
  • @user2065377 I *can* do that but I'd like to avoid doing that - it's not a one-time thing (it's an embedded database in an application) and there's the risk of missing fields etc this way I'm not sure how the link you sent is related, but I don't think you can do ``IF EXISTS`` with multiple columns in one statement...? – Jakg Feb 13 '19 at 20:53

0 Answers0