1

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:

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]') 
         AND name = 'ColumnName'
)
BEGIN
    --STUFF HERE
END

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
           From INFORMATION_SCHEMA.COLUMNS
           Where Table_Name = 'TabTessereVeicoli'
           And Column_Name = 'TAGA')
begin

    ALTER TABLE TabTessereVeicoli ADD TAGA text(25) NULL;

end

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?

3

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.

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.