Insert column if it doesn't exist

Question

I am trying to insert a new column in my database if it isn't inserted earlier. My column looks like this

ALTER TABLE dbo.tSafeUnit ADD HasAccess24_7 tinyint not null default 0

I want the column to be inserted only if it is not in the table.

My line of code only inserts it but doesn't check if it exists.

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. — a_horse_with_no_name, Dec 05 '17 at 15:52
Inserting rows is done via `insert` **not** via `ALTER TABLE` — a_horse_with_no_name, Dec 05 '17 at 15:52
If a column with the same name already exists you'll get an error. — jarlh, Dec 05 '17 at 15:54
[Edit] your post to tag the DBMS being used, then we'll definitely have a duplicate to mark. — underscore_d, Dec 05 '17 at 15:56
In Postgres you can do `alter table ... add column if not exists ...` - which DBMS **are** you using? — a_horse_with_no_name, Dec 05 '17 at 15:56
SQL Server: IF EXISTS ( SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[tSafeUnit]') AND name = 'HasAccess24_7' ) — user8834780, Dec 05 '17 at 15:56
Possible duplicate of [How to check if a column exists in SQL Server table](https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-sql-server-table) — Magnus, Dec 05 '17 at 15:57
Possible duplicate of [Add a column to a table, if it does not already exist](https://stackoverflow.com/questions/8870802/add-a-column-to-a-table-if-it-does-not-already-exist) — underscore_d, Dec 05 '17 at 15:57

score 2 · Accepted Answer · answered Dec 05 '17 at 16:00

2

Try this:

IF (SELECT COUNT(*) FROM syscolumns WHERE name = 'HasAccess24_7'
AND OBJECT_NAME(object_id) = 'tSafeUnit') = 0
BEGIN
    ALTER TABLE dbo.tSafeUnit ADD HasAccess24_7 tinyint not null default 0
END

answered Dec 05 '17 at 16:00

Joe Taras

14,775
7
39
53

1

Please note, syscolumns has been deprecated for quite some time now in favor of sys.columns. https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-syscolumns-transact-sql – Sean Lange Dec 05 '17 at 16:03
And using `IF EXISTS (...)` instead of `SELECT COUNT(*)` would also be advisable – marc_s Dec 05 '17 at 16:47

score 1 · Answer 2 · answered Dec 05 '17 at 15:56

1

If you are using MS SQL server, then you can do it like below :

IF COL_LENGTH('tSafeUnit', 'HasAccess24_7') IS NULL
BEGIN
    ALTER TABLE dbo.tSafeUnit 
    ADD HasAccess24_7 tinyint not null default 0
END

answered Dec 05 '17 at 15:56

Md. Suman Kabir

4,572
3
25
39

score 1 · Answer 3 · answered Dec 05 '17 at 16:12

1

You can try this.

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'HasAccess24_7' AND TABLE_NAME = 'tSafeUnit')
   AND EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tSafeUnit')
BEGIN
    ALTER TABLE dbo.tSafeUnit ADD HasAccess24_7 TINYINT NOT NULL DEFAULT 0
END

answered Dec 05 '17 at 16:12

Mova

874
1
6
23

not accurate at all.... what happen if HasAccess24_7 exists in table tSafeUnit_2 ? – MLeblanc Aug 12 '20 at 12:51
So what will happen? – Mova Aug 25 '20 at 00:33
if HasAccess24_7 exists in another table, then it will not add the new column, you need to make sure HasAccess24_7 doesn't exists in that particular table, and not exists in every table in the database – MLeblanc Aug 25 '20 at 02:42
hahaha... Just look at the condition "COLUMN_NAME = 'HasAccess24_7' AND TABLE_NAME = 'tSafeUnit'". Coming to "and not exists in every table in the database", why does it matter whether the same column is available in any other table or not. Is it even asked by Endrit Sheholli? – Mova Aug 27 '20 at 15:03

score -1 · Answer 4 · answered Aug 12 '20 at 12:57

-1

test is the column exists for that table in that schema

if not exists ( select 1 
                from information_schema.columns 
                where table_schema = 'dbo' 
                and table_name = 'tSafeUnit' 
                and column_name = 'HasAccess24_7')
begin
    ALTER TABLE dbo.tSafeUnit ADD HasAccess24_7 TINYINT NOT NULL DEFAULT 0;
end

answered Aug 12 '20 at 12:57

MLeblanc

1,733
11
20

What if tSafeUnit does not exist? – Mova Aug 25 '20 at 00:35
LOL... its always funny when we don't have any answer :P Just for your knowledge, it will throw an error... – Mova Aug 27 '20 at 15:04

Insert column if it doesn't exist

4 Answers4