create table if not exists, insert columns if missing, best way

Question

I need to create a table if it doesnt exist, and add missing columns in the proper order if the table already exists.

I know how to do it with lots of queries, and if statements and so on, but what I am asking here is what the best solution would be.. Maybe there is a special query to do this, or a smart way.

I would do it this way:

create table if not exists (all columns as they should be)
compare all the columns (if some are missing they will be added, else not)

Is this the best way or are there better ways to do it?

ADDITIONAL INFO

the colums need to be added at the right position. I have a list of strings representing all the columns in the proper order. using vb.net I am iterating through these strings.

Do you have data that you need to keep? If not, drop everything and then add it. — Paul Draper, Oct 31 '13 at 09:00

score 5 · Accepted Answer · edited May 23 '17 at 12:07

5

Check out this for instance. It's basically about querying the data dictionary and adding columns only if they do not exist:

IF NOT EXISTS(SELECT NULL
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE table_name = 'tablename'
                 AND table_schema = 'db_name'
                 AND column_name = 'columnname') THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

Putting it in a procedure makes it quite handy.

p.s. note about column positions: from the docs

To add a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

edited May 23 '17 at 12:07

Community

1
1

answered Oct 31 '13 at 09:04

Trinimon

13,839
9
44
60

thanks! does this also add the column in the right position? i mean, i have a list of strings representing the columns in vb.net and would need them to be added in the same order. so it could be that a column in the middle of the table is missing.. – sharkyenergy Oct 31 '13 at 09:22
1

Let me put it this way: it can! ;) If you call your `AddCol(...)` procedure in the order of the columns - then "yes". However, if you need to insert a column in between, you've to add the position using `AFTER`. Check the docs for details: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html – Trinimon Oct 31 '13 at 09:34

score -1 · Answer 2 · edited May 14 '14 at 09:40

-1

You can use following codes for that:

if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'table_name' and COLUMN_NAME = 'column_name')

BEGIN
    ALTER TABLE table_name ADD
    ToUser uniqueidentifier NULL

END

edited May 14 '14 at 09:40

Perry

11,172
2
27
37

answered Oct 31 '13 at 10:36

Yung Corral

1
1

create table if not exists, insert columns if missing, best way

2 Answers2