this is an easy question, but I can't find out the solution...

I have to check if a column exists and add it if the test is negative. How can I do with only sql code?

This is the syntax for SQL Server:

  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]') 
         AND name = 'ColumnName'

and for MS Access, using only SQL code... what is the right syntax to do it?

UPDATE: also a way to do a try cath statement would be ok, I only need to not add anything if it doesn't exist... so, also a try catch is ok, I think. Also try catch is easily possible to use in sql server... and for access?

UPDATE 2: I have done this:

If Not Exists (Select Column_Name
           Where Table_Name = 'TabTessereVeicoli'
           And Column_Name = 'TAGA')

    ALTER TABLE TabTessereVeicoli ADD TAGA text(25) NULL;


but I get the error "SQL statement not valid. Expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE." Why? How can I do an alter table after an IF?


Access doesn't support the IF EXISTS syntax in queries; I think your error is coming from that. It's also very difficult to do any sort of procedural logic using Access SQL. Your alternatives are to run the ALTER TABLE statement and handle the error elsewhere or use VBA to conditionally run the statement.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.