脚本使用If在sql server中不存在 [英] Script using If Does not exists in sql server

问题描述

大家好



你能帮我吗。



我必须添加一些列到现有的表格。





如果列不存在我必须编写脚本然后另外添加该表格。

任何人都可以给我一个例子。





谢谢



Suman

Hi All

Can you pls help me.

I have to add some columns to the existing table.


I have to write a script if column does not exist then add otherwise alter that table.
Can anyone pls give me an example.


Thanks

Suman

推荐答案

尝试：

Try:

IF NOT EXISTS
  ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'MyTable' AND column_name = 'MyNewColumn'
  )
  BEGIN
    ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL default 1;
  END

首先，如上所述，你不能有两列同一个表中的同名。由于您必须添加列并更改它，然后只需增强上面的脚本：

First, as noted above, you can not have two columns with the same name in the same table. Since you must add the column and alter it, then simply enhance the script above:

IF NOT EXISTS
  ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'MyTable' AND column_name = 'MyNewColumn'
  )
  BEGIN
    ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL default 1;
  END
ELSE
  BEGIN
   -- Do whatever needs to be done for adding an existing columns
  END

这篇关于脚本使用If在sql server中不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！