The exists of the if not in MSSQL and SQLite _mssql

In the SQL name, if not exists if it does not exist, if exists if it exists.

Below, learn the use of both.

A to determine if the database does not exist

Copy Code code as follows:

If not EXISTS (SELECT * from sys.databases WHERE name = ' database_name ')

b, when the table does not exist

Copy Code code as follows:

If not EXISTS (SELECT * from sysobjects where id = object_id (' table_name ') and OBJECTPROPERTY (ID, ' isusertable ') = 1)

C, the Judge column does not exist

Copy Code code as follows:

If not EXISTS (SELECT * from syscolumns where id=object_id (' table_name ') and Name= ' column_name ')

When the table is not saved, I can execute the creation of the database, create a table, add columns, you can execute the corresponding SQL statements;

If exists the same judgment, first judge whether the query results exist, if there is a statement after the execution judgment, query the database, table, column method is the same;

MSSQL Syntax:

Copy Code code as follows:

If not exists (SELECT 1 from [t_table] where [fName] = ' John ')
insert INTO [t_table] ([fName]) VALUES (' John ');

SQLite syntax:

Copy Code code as follows:

insert INTO [t_table] ([fName]) Select ' John '
Where NOT EXISTS (SELECT 1 from [t_table] where [fName] = ' John ');